def get_available_resources(start, end):
"""get all resources that are not booked for a sitting
in the given time period
"""
assert (type(start) == datetime.datetime)
assert (type(end) == datetime.datetime)
session = Session()
#start_end={'start': start, 'end':end}
sql_booked_resources = """
SELECT resources.resource_id AS resources_resource_id
FROM resources JOIN resourcebookings
ON resources.resource_id = resourcebookings.resource_id
JOIN group_sittings
ON resourcebookings.sitting_id = group_sittings.sitting_id
WHERE group_sittings.start_date BETWEEN :start AND :end
OR group_sittings.end_date BETWEEN :start AND :end
OR :start BETWEEN group_sittings.start_date AND group_sittings.end_date
OR :end BETWEEN group_sittings.start_date AND group_sittings.end_date
"""
sql_resources = """
SELECT resources_1.resource_id AS resources_1_resource_id
FROM resources AS resources_1
WHERE resources_1.resource_id NOT IN (%s) """ % sql_booked_resources
connection = session.connection(domain.Resource)
query = connection.execute(rdb.text(sql_resources), start=start, end=end)
resources = query.fetchall()
return resources
def get_available_resources(start, end):
"""get all resources that are not booked for a sitting
in the given time period
"""
assert(type(start) == datetime.datetime)
assert(type(end) == datetime.datetime)
session = Session()
#start_end={'start': start, 'end':end}
sql_booked_resources = """
SELECT resources.resource_id AS resources_resource_id
FROM resources JOIN resourcebookings
ON resources.resource_id = resourcebookings.resource_id
JOIN sitting
ON resourcebookings.sitting_id = sitting.sitting_id
WHERE sitting.start_date BETWEEN :start AND :end
OR sitting.end_date BETWEEN :start AND :end
OR :start BETWEEN sitting.start_date AND sitting.end_date
OR :end BETWEEN sitting.start_date AND sitting.end_date
"""
sql_resources = """
SELECT resources_1.resource_id AS resources_1_resource_id
FROM resources AS resources_1
WHERE resources_1.resource_id NOT IN (%s) """ % sql_booked_resources
connection = session.connection(domain.Resource)
query = connection.execute(sa.text(sql_resources), start=start, end=end)
resources= query.fetchall()
return resources
def deactivateGroupMembers(group):
""" upon dissolution of a group all group members
are deactivated and get the end date of the group"""
session = Session()
group_id = group.group_id
end_date = group.end_date
assert (end_date != None)
connection = session.connection(domain.Group)
connection.execute(schema.user_group_memberships.update().where(
rdb.and_(schema.user_group_memberships.c.group_id == group_id,
schema.user_group_memberships.c.active_p == True)).values(
active_p=False))
connection.execute(schema.user_group_memberships.update().where(
rdb.and_(schema.user_group_memberships.c.group_id == group_id,
schema.user_group_memberships.c.end_date == None)).values(
end_date=end_date))
def deactivateGroupMemberTitles(group):
group_members = rdb.select(
[schema.user_group_memberships.c.user_id],
schema.user_group_memberships.c.group_id == group_id)
connection.execute(schema.role_titles.update().where(
rdb.and_(schema.role_titles.c.membership_id.in_(group_members),
schema.role_titles.c.end_date == None)).values(
end_date=end_date))
deactivateGroupMemberTitles(group)
def get_offices_held_for_user_in_parliament(user_id, parliament_id):
""" get the Offices (functions/titles) held by a user in a parliament """
session = Session()
connection = session.connection(domain.Group)
group_ids = get_all_group_ids_in_parliament(parliament_id)
offices_held = rdb.select([schema.groups.c.short_name,
schema.groups.c.full_name,
schema.groups.c.type,
schema.user_role_types.c.user_role_name,
schema.role_titles.c.start_date,
schema.role_titles.c.end_date,
schema.user_group_memberships.c.start_date,
schema.user_group_memberships.c.end_date,
],
from_obj=[
rdb.join(schema.groups, schema.user_group_memberships,
schema.groups.c.group_id == schema.user_group_memberships.c.group_id
).outerjoin(
schema.role_titles, schema.user_group_memberships.c.membership_id ==
schema.role_titles.c.membership_id).outerjoin(
schema.user_role_types,
schema.role_titles.c.title_name_id ==
schema.user_role_types.c.user_role_type_id)],
whereclause=rdb.and_(
schema.groups.c.group_id.in_(group_ids),
schema.user_group_memberships.c.user_id == user_id),
order_by=[schema.user_group_memberships.c.start_date,
schema.user_group_memberships.c.end_date,
schema.role_titles.c.start_date,
schema.role_titles.c.end_date]
)
o_held = connection.execute(offices_held)
return o_held
def get_groups_held_for_user_in_parliament(user_id, parliament_id):
""" get the Offices (functions/titles) held by a user """
session = Session()
connection = session.connection(domain.Group)
group_ids = get_all_group_ids_in_parliament(parliament_id)
#!+MODELS(miano, 16 march 2011) Why are these queries hardcorded?
#TODO:Fix this
offices_held = rdb.select([schema.group.c.short_name,
schema.group.c.full_name,
schema.group.c.type,
schema.title_type.c.title_name,
schema.member_title.c.start_date,
schema.member_title.c.end_date,
schema.user_group_membership.c.start_date,
schema.user_group_membership.c.end_date,
],
from_obj=[
rdb.join(schema.group, schema.user_group_membership,
schema.group.c.group_id == schema.user_group_membership.c.group_id
).outerjoin(
schema.member_title, schema.user_group_membership.c.membership_id ==
schema.member_title.c.membership_id).outerjoin(
schema.title_type,
schema.member_title.c.title_type_id ==
schema.title_type.c.title_type_id)],
whereclause=rdb.and_(
schema.group.c.group_id.in_(group_ids),
schema.user_group_membership.c.user_id == user_id),
order_by=[schema.user_group_membership.c.start_date,
schema.user_group_membership.c.end_date,
schema.member_title.c.start_date,
schema.member_title.c.end_date]
)
o_held = connection.execute(offices_held)
return o_held
def execute_sql(sql_statement, **kwargs):
"""Evaluates sql_statement for parameter values specified in kwargs
and executes it
"""
session = Session()
connection = session.connection(domain.Parliament)
query = connection.execute(sql.text(sql_statement), **kwargs)
return query
def execute_sql(sql_statement, **kwargs):
"""Evaluates sql_statement for parameter values specified in kwargs
and executes it
"""
session = Session()
connection = session.connection(domain.Parliament)
query = connection.execute(sql.text(sql_statement), **kwargs)
return query
def setMotionSerialNumber(motion):
"""
Number that indicate the order in which motions have been approved
by the Speaker. The Number is reset at the start of each new session
with the first motion assigned the number 1
"""
session = Session()
connection = session.connection(domain.Motion)
sequence = rdb.Sequence("motion_number_sequence")
motion.motion_number = connection.execute(sequence)
def setMotionSerialNumber(motion):
"""
Number that indicate the order in which motions have been approved
by the Speaker. The Number is reset at the start of each new session
with the first motion assigned the number 1
"""
session = Session()
connection = session.connection(domain.Motion)
sequence = rdb.Sequence('motion_number_sequence')
motion.motion_number = connection.execute(sequence)
def setQuestionSerialNumber(question):
"""
Approved questions are given a serial number enabling the clerks office
to record the order in which questions are received and hence enforce
a first come first served policy in placing the questions on the order
paper. The serial number is re-initialized at the start of each session
"""
session = Session()
connection = session.connection(domain.Question)
sequence = rdb.Sequence("question_number_sequence")
question.question_number = connection.execute(sequence)
def setQuestionSerialNumber(question):
"""
Approved questions are given a serial number enabling the clerks office
to record the order in which questions are received and hence enforce
a first come first served policy in placing the questions on the order
paper. The serial number is re-initialized at the start of each session
"""
session = Session()
connection = session.connection(domain.Question)
sequence = rdb.Sequence("question_number_sequence")
question.question_number = connection.execute(sequence)
def GET(self):
if "auth" in self.request.keys():
cols = [users.c.user_id]
if self.request["auth"] != "None":
cols.extend([users.c.password, users.c.salt])
session = Session()
connection = session.connection(domain.Group)
res = connection.execute(
rdb.select(
cols,
rdb.and_(users.c.login == self.request["login"],
users.c.active_p == "A")))
uid_tuple = res.fetchone()
if not uid_tuple:
return None
if self.request["auth"] != "None":
return self.json_response(str(uid_tuple))
return self.json_response(uid_tuple[0])
elif "login" in self.request.keys():
session = Session()
user = session.query(domain.User).filter(
rdb.and_(domain.User.login == self.request["login"],
domain.User.active_p == "A")).one()
return self.json_response(user)
elif "user_name" in self.request.keys():
session = Session()
user_values = session.query(domain.User).filter(
domain.User.login == self.request["user_name"]).all()
data = {}
if len(user_values) == 1:
b_user = user_values[0]
data = {
"fullname":
u"%s %s" % (b_user.first_name, b_user.last_name),
"email": b_user.email or u"",
"description": b_user.description or u"",
"notification": b_user.receive_notification or False,
}
return self.json_response(data)
else:
user_values = users.select([users.c.login],
users.c.active_p == "A").execute()
if "user_manager_id" in self.request.keys():
plugin_id = self.request["user_manager_id"]
data = [
dict(id=safeencode(r["login"]),
login=safeencode(r["login"]),
pluginid=plugin_id) for r in user_values
]
else:
data = tuple([safeencode(r["login"]) for r in user_values])
return self.json_response(data)
def PUT(self):
session = Session()
principal_id = simplejson.loads(self.request["principal_id"])
connection = session.connection(domain.Group)
# update existing
connection.execute(
security_schema.principal_role_map.update().where(
sa.and_(
security_schema.principal_role_map.c.principal_id==principal_id,
security_schema.principal_role_map.c.object_type==None,
security_schema.principal_role_map.c.object_id==None)).values(
setting=False))
def PUT(self):
session = Session()
principal_id = simplejson.loads(self.request["principal_id"])
connection = session.connection(domain.Group)
# update existing
connection.execute(
security_schema.principal_role_map.update().where(
sa.and_(
security_schema.principal_role_map.c.principal_id==principal_id,
security_schema.principal_role_map.c.object_type==None,
security_schema.principal_role_map.c.object_id==None)).values(
setting=False))
def GET(self):
if "auth" in self.request.keys():
cols = [user.c.user_id]
if self.request["auth"] != "None":
cols.extend([user.c.password, user.c.salt])
session = Session()
connection = session.connection(domain.Group)
res = connection.execute(sa.select(cols,
sa.and_(
user.c.login == self.request["login"],
user.c.active_p == "A"
)))
uid_tuple = res.fetchone()
if not uid_tuple:
return None
if self.request["auth"] != "None":
return self.json_response(str(uid_tuple))
return self.json_response(uid_tuple[0])
elif "login" in self.request.keys():
session = Session()
usr = session.query(domain.User).filter(
sa.and_(
domain.User.login == self.request["login"],
domain.User.active_p == "A")
).one()
return self.json_response(usr)
elif "user_name" in self.request.keys():
session = Session()
user_values = session.query(domain.User).filter(
domain.User.login == self.request["user_name"]).all()
data = {}
if len(user_values) == 1:
b_user = user_values[0]
data = {
"combined_name": b_user.combined_name,
"email": b_user.email or u"",
"description": b_user.description or u"",
"notification": b_user.receive_notification or False,
}
return self.json_response(data)
else:
user_values = user.select(
[user.c.login], user.c.active_p == "A").execute()
if "user_manager_id" in self.request.keys():
plugin_id = self.request["user_manager_id"]
data = [dict(id=safeencode(r["login"]),
login=safeencode(r["login"]),
pluginid=plugin_id) for r in user_values]
else:
data = tuple([safeencode(r["login"]) for r in user_values])
return self.json_response(data)
def deactivateGroupMemberTitles(group):
session = Session()
group_id = group.group_id
end_date = group.end_date
assert (end_date != None)
connection = session.connection(domain.Group)
group_members = rdb.select(
[schema.user_group_memberships.c.user_id],
schema.user_group_memberships.c.group_id == group_id)
connection.execute(schema.role_titles.update().where(
rdb.and_(
schema.role_titles.c.membership_id.in_(group_members),
schema.role_titles.c.end_date == None)).values(end_date=end_date))
def deactivateGroupMemberTitles(group):
session = Session()
group_id = group.group_id
end_date = group.end_date
assert(end_date != None)
connection = session.connection(domain.Group)
group_members = rdb.select([schema.user_group_memberships.c.user_id],
schema.user_group_memberships.c.group_id == group_id)
connection.execute(schema.role_titles.update(
).where(rdb.and_(
schema.role_titles.c.membership_id.in_(group_members),
schema.role_titles.c.end_date == None)
).values(end_date=end_date)
)
def get_group_ids_for_user_in_parliament(user_id, parliament_id):
""" get the groups a user is member of for a specific parliament """
session = Session()
connection = session.connection(domain.Group)
group_ids = get_all_group_ids_in_parliament(parliament_id)
my_groups = rdb.select([schema.user_group_memberships.c.group_id],
rdb.and_(schema.user_group_memberships.c.active_p == True,
schema.user_group_memberships.c.user_id == user_id,
schema.user_group_memberships.c.group_id.in_(group_ids)),
distinct=True)
my_group_ids = []
for group_id in connection.execute(my_groups):
my_group_ids.append(group_id[0])
return my_group_ids
def get_group_ids_for_user_in_parliament(user_id, parliament_id):
""" get the groups a user is member of for a specific parliament """
session = Session()
connection = session.connection(domain.Group)
group_ids = get_all_group_ids_in_parliament(parliament_id)
my_groups = rdb.select([schema.user_group_memberships.c.group_id],
rdb.and_(schema.user_group_memberships.c.active_p == True,
schema.user_group_memberships.c.user_id == user_id,
schema.user_group_memberships.c.group_id.in_(group_ids)),
distinct=True)
my_group_ids = []
for group_id in connection.execute(my_groups):
my_group_ids.append(group_id[0])
return my_group_ids
def deactivateGroupMembers(group):
""" upon dissolution of a group all group members
are deactivated and get the end date of the group"""
session = Session()
group_id = group.group_id
end_date = group.end_date
assert end_date != None
connection = session.connection(domain.Group)
connection.execute(
schema.user_group_memberships.update()
.where(
rdb.and_(
schema.user_group_memberships.c.group_id == group_id, schema.user_group_memberships.c.active_p == True
)
)
.values(active_p=False)
)
connection.execute(
schema.user_group_memberships.update()
.where(
rdb.and_(
schema.user_group_memberships.c.group_id == group_id, schema.user_group_memberships.c.end_date == None
)
)
.values(end_date=end_date)
)
def deactivateGroupMemberTitles(group):
group_members = rdb.select(
[schema.user_group_memberships.c.user_id], schema.user_group_memberships.c.group_id == group_id
)
connection.execute(
schema.member_titles.update()
.where(
rdb.and_(
schema.member_titles.c.membership_id.in_(group_members), schema.member_titles.c.end_date == None
)
)
.values(end_date=end_date)
)
deactivateGroupMemberTitles(group)
def get_groups_held_for_user_in_parliament(user_id, parliament_id):
""" get the Offices (functions/titles) held by a user """
session = Session()
connection = session.connection(domain.Group)
all_offices = session.query(domain.Office).all()
group_ids = get_all_group_ids_in_parliament(parliament_id)
#group_ids = [office.group_id for office in all_offices]
#!+MODELS(miano, 16 march 2011) Why are these queries hardcorded?
#TODO:Fix this
offices_held = rdb.select(
[
schema.groups.c.short_name,
schema.groups.c.full_name,
schema.groups.c.type,
schema.title_types.c.title_name,
schema.member_titles.c.start_date,
schema.member_titles.c.end_date,
schema.user_group_memberships.c.start_date,
schema.user_group_memberships.c.end_date,
],
from_obj=[
rdb.join(
schema.groups, schema.user_group_memberships,
schema.groups.c.group_id ==
schema.user_group_memberships.c.group_id).outerjoin(
schema.member_titles,
schema.user_group_memberships.c.membership_id ==
schema.member_titles.c.membership_id).outerjoin(
schema.title_types,
schema.member_titles.c.title_type_id ==
schema.title_types.c.title_type_id)
],
whereclause=rdb.and_(
schema.groups.c.group_id.in_(group_ids),
schema.user_group_memberships.c.user_id == user_id),
order_by=[
schema.user_group_memberships.c.start_date,
schema.user_group_memberships.c.end_date,
schema.member_titles.c.start_date, schema.member_titles.c.end_date
])
o_held = connection.execute(offices_held)
return o_held
def GET(self):
session = Session()
connection = session.connection(domain.Group)
principal_id = self.request["principal_id"]
mappings = connection.execute(sa.select(
[security_schema.principal_role_map.c.role_id],
sa.and_(
sa.or_(
security_schema.principal_role_map.c.object_type==None,
security_schema.principal_role_map.c.object_type==
"parliament"
),
security_schema.principal_role_map.c.principal_id==principal_id,
security_schema.principal_role_map.c.setting==True)))
role_names = []
for (role_id,) in mappings:
role_names.append(role_id)
return self.json_response(role_names)
def POST(self):
session = Session()
setting = simplejson.loads(self.request["setting"])
roles = simplejson.loads(self.request["roles"])
principal_id = simplejson.loads(self.request["principal_id"])
connection = session.connection(domain.Group)
# insert new global mappings
for role_id in tuple(roles):
connection.execute(
security_schema.principal_role_map.insert().values(
principal_id=principal_id,
role_id=role_id,
setting=setting,
object_type=None,
object_id=None))
# remove from roles so other plugins won't attempt to
# assign as well
roles.remove(role_id)
def POST(self):
session = Session()
setting = simplejson.loads(self.request["setting"])
roles = simplejson.loads(self.request["roles"])
principal_id = simplejson.loads(self.request["principal_id"])
connection = session.connection(domain.Group)
# insert new global mappings
for role_id in tuple(roles):
connection.execute(
security_schema.principal_role_map.insert().values(
principal_id=principal_id,
role_id=role_id,
setting=setting,
object_type=None,
object_id=None))
# remove from roles so other plugins won't attempt to
# assign as well
roles.remove(role_id)
def GET(self):
session = Session()
connection = session.connection(domain.Group)
principal_id = self.request["principal_id"]
mappings = connection.execute(sa.select(
[security_schema.principal_role_map.c.role_id],
sa.and_(
sa.or_(
security_schema.principal_role_map.c.object_type==None,
security_schema.principal_role_map.c.object_type==
"chamber"
),
security_schema.principal_role_map.c.principal_id==principal_id,
security_schema.principal_role_map.c.setting==True)))
role_names = []
for (role_id,) in mappings:
role_names.append(role_id)
return self.json_response(role_names)
def GET(self):
session = Session()
connection = session.connection(domain.Group)
plugin_id = self.request["plugin_id"]
mappings = connection.execute(
rdb.select([security_schema.principal_role_map.c.role_id]))
role_ids = []
for (role_id, ) in mappings:
role_ids.append(role_id)
role_ids = []
for (role_id, ) in mappings:
role_ids.append(role_id)
return self.json_response([{
"id": role_id,
"pluginid": plugin_id,
} for role_id in role_ids])
return self.json_response(mappings)
def GET(self):
session = Session()
connection = session.connection(domain.Group)
plugin_id = self.request["plugin_id"]
mappings = connection.execute(sa.select(
[security_schema.principal_role_map.c.role_id]))
role_ids = []
for (role_id,) in mappings:
role_ids.append(role_id)
role_ids = []
for (role_id,) in mappings:
role_ids.append(role_id)
return self.json_response([{
"id": role_id,
"pluginid": plugin_id,
} for role_id in role_ids])
return self.json_response(mappings)
def get_offices_held_for_user_in_parliament(user_id, parliament_id):
""" get the Offices (functions/titles) held by a user in a parliament """
session = Session()
connection = session.connection(domain.Group)
group_ids = get_all_group_ids_in_parliament(parliament_id)
offices_held = rdb.select(
[
schema.groups.c.short_name,
schema.groups.c.full_name,
schema.groups.c.type,
schema.user_role_types.c.user_role_name,
schema.role_titles.c.start_date,
schema.role_titles.c.end_date,
schema.user_group_memberships.c.start_date,
schema.user_group_memberships.c.end_date,
],
from_obj=[
rdb.join(
schema.groups, schema.user_group_memberships,
schema.groups.c.group_id ==
schema.user_group_memberships.c.group_id).outerjoin(
schema.role_titles,
schema.user_group_memberships.c.membership_id ==
schema.role_titles.c.membership_id).outerjoin(
schema.user_role_types,
schema.role_titles.c.title_name_id ==
schema.user_role_types.c.user_role_type_id)
],
whereclause=rdb.and_(
schema.groups.c.group_id.in_(group_ids),
schema.user_group_memberships.c.user_id == user_id),
order_by=[
schema.user_group_memberships.c.start_date,
schema.user_group_memberships.c.end_date,
schema.role_titles.c.start_date, schema.role_titles.c.end_date
])
o_held = connection.execute(offices_held)
return o_held
def setRegistryNumber(item):
session = Session()
connection = session.connection(domain.ParliamentaryItem)
sequence = rdb.Sequence('registry_number_sequence')
item.registry_number = connection.execute(sequence)
def set_pi_registry_number(item, registry_number):
session = Session()
connection = session.connection(domain.ParliamentaryItem)
item.registry_number = registry_number
def get_next_reg():
session = Session()
sequence = rdb.Sequence("registry_number_sequence")
connection = session.connection(domain.ParliamentaryItem)
return connection.execute(sequence)
def get_next_prog(context):
session = Session()
sequence = rdb.Sequence("%s_registry_sequence" % context.type)
connection = session.connection(context.__class__)
return connection.execute(sequence)
def setTabledDocumentSerialNumber(tabled_document):
session = Session()
connection = session.connection(domain.TabledDocument)
sequence = rdb.Sequence("tabled_document_number_sequence")
tabled_document.tabled_document_number = connection.execute(sequence)
def set_pi_registry_number(item):
session = Session()
connection = session.connection(domain.ParliamentaryItem)
sequence = rdb.Sequence("registry_number_sequence")
item.registry_number = connection.execute(sequence)
def get_next_reg():
session = Session()
sequence = rdb.Sequence("registry_number_sequence")
connection = session.connection(domain.ParliamentaryItem)
return connection.execute(sequence)
def setTabledDocumentSerialNumber(tabled_document):
session = Session()
connection = session.connection(domain.TabledDocument)
sequence = rdb.Sequence('tabled_document_number_sequence')
tabled_document.tabled_document_number = connection.execute(sequence)
def get_next_prog(context):
session = Session()
sequence = rdb.Sequence("%s_registry_sequence" % context.type)
connection = session.connection(context.__class__)
return connection.execute(sequence)
def set_pi_registry_number(item, mask):
session = Session()
connection = session.connection(domain.ParliamentaryItem)
item.registry_number = mask