class AccountStatistics(Base):
"""Statistically relevant data concerning an account."""
__tablename__ = "account"
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
account_type = schema.Column(Enum(["guest", "converted", "full"]),
default="full",
nullable=True)
creation_date = schema.Column(types.DateTime,
nullable=True,
default=functions.now())
class CompanyStatistics(Base):
"""Statistically relevant data concerning a company."""
__tablename__ = "company"
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
country = schema.Column(types.String(3))
employees = schema.Column(
Enum(["no answer", "1-9", "10-49", "50-249", "250+"]))
conductor = schema.Column(
Enum(["no answer", "staff", "third-party", "both"]))
referer = schema.Column(
Enum([
"no answer",
"employers-organisation",
"trade-union",
"national-public-institution",
"eu-institution",
"health-safety-experts",
"other",
]))
workers_participated = schema.Column(Enum(["no answer", "yes", "no"]))
needs_met = schema.Column(Enum(["no answer", "yes", "no"]))
recommend_tool = schema.Column(Enum(["no answer", "yes", "no"]))
date = schema.Column(types.DateTime(), nullable=True)
tool_path = schema.Column(types.String(512), nullable=False)
class Company(BaseObject):
"""Information about a company."""
__tablename__ = "company"
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
session_id = schema.Column(
types.Integer(),
schema.ForeignKey("session.id", onupdate="CASCADE",
ondelete="CASCADE"),
nullable=False,
index=True,
)
session = orm.relation(
"SurveySession",
cascade="all,delete-orphan",
single_parent=True,
backref=orm.backref("company", uselist=False, cascade="all"),
)
country = schema.Column(types.String(3))
employees = schema.Column(Enum([None, "1-9", "10-49", "50-249", "250+"]))
conductor = schema.Column(Enum([None, "staff", "third-party", "both"]))
referer = schema.Column(
Enum([
None,
"employers-organisation",
"trade-union",
"national-public-institution",
"eu-institution",
"health-safety-experts",
"other",
]))
workers_participated = schema.Column(types.Boolean())
needs_met = schema.Column(types.Boolean())
recommend_tool = schema.Column(types.Boolean())
timestamp = schema.Column(types.DateTime(), nullable=True)
class ActionPlan(BaseObject):
"""Action plans for a known risk."""
__tablename__ = "action_plan"
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
risk_id = schema.Column(
types.Integer(),
schema.ForeignKey(Risk.id, onupdate="CASCADE", ondelete="CASCADE"),
nullable=False,
index=True,
)
action_plan = schema.Column(types.UnicodeText())
prevention_plan = schema.Column(types.UnicodeText())
# The column "action" is the synthesis of "action_plan" and "prevention_plan"
action = schema.Column(types.UnicodeText())
requirements = schema.Column(types.UnicodeText())
responsible = schema.Column(types.Unicode(256))
budget = schema.Column(types.Integer())
planning_start = schema.Column(types.Date())
planning_end = schema.Column(types.Date())
reference = schema.Column(types.Text())
plan_type = schema.Column(
Enum([
"measure_custom",
"measure_standard",
"in_place_standard",
"in_place_custom",
]),
nullable=False,
index=True,
default="measure_custom",
)
solution_id = schema.Column(types.Unicode(20))
used_in_training = schema.Column(
types.Boolean(),
default=True,
index=True,
)
risk = orm.relation(
Risk,
backref=orm.backref("action_plans",
order_by=id,
cascade="all, delete, delete-orphan"),
)
class SurveySessionStatistics(Base):
"""Statistically relevant data concerning a session."""
__tablename__ = "assessment"
tool_path = schema.Column(types.String(512), nullable=False)
completion_percentage = schema.Column(types.Integer,
nullable=True,
default=0)
account_type = schema.Column(Enum(["guest", "converted", "full"]),
default="full",
nullable=True)
start_date = schema.Column(types.DateTime,
nullable=False,
default=functions.now())
country = schema.Column(types.String(512), nullable=False)
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
account_id = schema.Column(types.Integer(), nullable=True)
class DutchCompany(BaseObject):
"""Information about a Dutch company."""
__tablename__ = "dutch_company"
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
session_id = schema.Column(
types.Integer(),
schema.ForeignKey("session.id", onupdate="CASCADE", ondelete="CASCADE"),
nullable=False,
index=True,
)
session = orm.relation(
"SurveySession",
cascade="all,delete-orphan",
single_parent=True,
backref=orm.backref("dutch_company", uselist=False, cascade="all"),
)
title = schema.Column(types.Unicode(128))
address_visit_address = schema.Column(types.UnicodeText())
address_visit_postal = schema.Column(types.Unicode(16))
address_visit_city = schema.Column(types.Unicode(64))
address_postal_address = schema.Column(types.UnicodeText())
address_postal_postal = schema.Column(types.Unicode(16))
address_postal_city = schema.Column(types.Unicode(64))
email = schema.Column(types.String(128))
phone = schema.Column(types.String(32))
activity = schema.Column(types.Unicode(64))
submitter_name = schema.Column(types.Unicode(64))
submitter_function = schema.Column(types.Unicode(64))
department = schema.Column(types.Unicode(64))
location = schema.Column(types.Unicode(64))
submit_date = schema.Column(types.Date(), default=functions.now())
employees = schema.Column(Enum([None, "40h", "max25", "over25"]))
absentee_percentage = schema.Column(types.Numeric(precision=5, scale=2))
accidents = schema.Column(types.Integer())
incapacitated_workers = schema.Column(types.Integer())
arbo_expert = schema.Column(types.Unicode(128))
works_council_approval = schema.Column(types.Date())
class Account(BaseObject):
"""A user account. Users have to register with euphorie before they can
start a survey session. A single account can have multiple survey sessions.
"""
__tablename__ = "account"
id = schema.Column(
types.Integer(),
primary_key=True,
autoincrement=True,
)
loginname = schema.Column(
types.String(255),
nullable=False,
index=True,
unique=True,
)
password = schema.Column(types.Unicode(64))
tc_approved = schema.Column(types.Integer())
account_type = schema.Column(
Enum(["guest", "converted", "full"]),
default="full",
nullable=True,
)
group_id = schema.Column(
types.Unicode(32),
schema.ForeignKey("group.group_id"),
)
created = schema.Column(
types.DateTime,
nullable=True,
default=functions.now(),
)
last_login = schema.Column(
types.DateTime,
nullable=True,
default=None,
)
first_name = schema.Column(
types.Unicode(),
nullable=True,
default=None,
)
last_name = schema.Column(
types.Unicode(),
nullable=True,
default=None,
)
@property
def groups(self):
group = self.group
if not group:
return []
groups = [group]
groups.extend(group.descendants)
return groups
@property
def acquired_sessions(self):
"""The session the account acquires because he belongs to a group."""
group = self.group
if not group:
return []
return list(group.acquired_sessions)
@property
def group_sessions(self):
"""The session the account acquires because he belongs to a group."""
group = self.group
if not group:
return []
return list(group.sessions)
@property
def email(self):
"""Email addresses are used for login, return the login."""
return self.loginname
@property
def login(self):
"""This synchs naming with :obj:`euphorie.content.user.IUser` and is
needed by the authentication tools.
"""
return self.loginname
def getUserName(self):
"""Return the login name."""
return self.loginname
def getRoles(self):
"""Return all global roles for this user."""
return ("EuphorieUser", )
def getRolesInContext(self, object):
"""Return the roles of the user in the current context (same as
:obj:`getRoles`).
"""
return self.getRoles()
def getDomains(self):
return []
def addPropertysheet(self, propfinder_id, data):
pass
def _addGroups(self, group_ids):
pass
def _addRoles(self, role_ids):
pass
def has_permission(self, perm, context):
"""Check if the user has a permission in a context."""
return perm == "Euphorie: View a Survey"
def allowed(self, object, object_roles=None):
"""Check if this account has any of the requested roles in the context
of `object`."""
if object_roles is _what_not_even_god_should_do:
return False
if object_roles is None:
return True
for role in ["Anonymous", "Authenticated", "EuphorieUser"]:
if role in object_roles:
return True
return False
@ram.cache(_forever_cache_key)
def verify_password(self, password):
"""Verify the given password against the one
stored in the account table
"""
if not password:
return False
if not isinstance(password, six.string_types):
return False
if password == self.password:
return True
password = safe_nativestring(password)
return bcrypt.checkpw(password, self.password)
def hash_password(self):
"""hash the account password using bcrypt"""
try:
password = self.password
except AttributeError:
return
if not password:
return
password = safe_nativestring(password)
if BCRYPTED_PATTERN.match(password):
# The password is already encrypted, do not encrypt it again
# XXX this is broken with passwords that are actually an hash
return
self.password = safe_unicode(
bcrypt.hashpw(
password,
bcrypt.gensalt(),
))
class SurveyTreeItem(BaseObject):
"""A tree of questions.
The data is stored in the form of a materialized tree. The path is built
using a list of item numbers. Each item number has three digits and uses
0-prefixing to make sure we can use simple string sorting to produce a
sorted tree.
"""
__tablename__ = "tree"
__table_args__ = (
schema.UniqueConstraint("session_id", "path"),
schema.UniqueConstraint("session_id", "zodb_path", "profile_index"),
{},
)
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
session_id = schema.Column(
types.Integer(),
schema.ForeignKey("session.id", onupdate="CASCADE",
ondelete="CASCADE"),
nullable=False,
index=True,
)
parent_id = schema.Column(
types.Integer(),
schema.ForeignKey("tree.id", onupdate="CASCADE", ondelete="CASCADE"),
nullable=True,
index=True,
)
type = schema.Column(
Enum(["risk", "module"]),
nullable=False,
index=True,
)
path = schema.Column(
types.String(40),
nullable=False,
index=True,
)
has_description = schema.Column(
types.Boolean(),
default=False,
index=True,
)
zodb_path = schema.Column(
types.String(512),
nullable=False,
)
profile_index = schema.Column(
types.Integer(),
default=0,
nullable=False,
)
depth = schema.Column(
types.Integer(),
default=0,
nullable=False,
index=True,
)
title = schema.Column(types.Unicode(512))
postponed = schema.Column(types.Boolean())
skip_children = schema.Column(
types.Boolean(),
default=False,
nullable=False,
)
__mapper_args__ = dict(polymorphic_on=type)
session = orm.relation(
"SurveySession",
cascade="all",
)
# parent = orm.relation("SurveyTreeItem", uselist=False)
@property
def parent(self):
# XXX Evil! Figure out why the parent relation does not work
return self.parent_id and Session.query(SurveyTreeItem).get(
self.parent_id)
def getId(self):
return self.path[-3:].lstrip("0")
@property
def short_path(self):
def slice(path):
while path:
yield path[:3].lstrip("0")
path = path[3:]
return slice(self.path)
@property
def number(self):
return ".".join(self.short_path)
def children(self, filter=None):
query = (Session.query(SurveyTreeItem).filter(
SurveyTreeItem.session_id == self.session_id).filter(
SurveyTreeItem.depth == self.depth + 1))
if self.path:
query = query.filter(SurveyTreeItem.path.like(self.path + "%"))
if filter is not None:
query = query.filter(filter)
return query.order_by(SurveyTreeItem.path)
def siblings(self, klass=None, filter=None):
if not self.path:
return []
if klass is None:
klass = SurveyTreeItem
query = (Session.query(klass).filter(
klass.session_id == self.session_id).filter(
klass.parent_id == self.parent_id))
if filter is not None:
query = query.filter(sql.or_(klass.id == self.id, filter))
return query.order_by(klass.path)
def addChild(self, item):
sqlsession = Session()
query = (sqlsession.query(SurveyTreeItem.path).filter(
SurveyTreeItem.session_id == self.session_id).filter(
SurveyTreeItem.depth == self.depth + 1))
if self.path:
query = query.filter(SurveyTreeItem.path.like(self.path + "%"))
last = query.order_by(SurveyTreeItem.path.desc()).first()
if not last:
index = 1
else:
index = int(last[0][-3:]) + 1
item.session = self.session
item.depth = self.depth + 1
item.path = (self.path or "") + "%03d" % index
item.parent_id = self.id
if self.profile_index != -1:
item.profile_index = self.profile_index
sqlsession.add(item)
self.session.touch()
return item
def removeChildren(self, excluded=[]):
if self.id not in excluded:
excluded.append(self.id)
session = Session()
if self.path:
filter = sql.and_(
SurveyTreeItem.session_id == self.session_id,
SurveyTreeItem.path.like(self.path + "%"),
sql.not_(SurveyTreeItem.id.in_(excluded)),
)
else:
filter = sql.and_(
SurveyTreeItem.session_id == self.session_id,
sql.not_(SurveyTreeItem.id.in_(excluded)),
)
removed = session.query(SurveyTreeItem).filter(filter).all()
session.execute(SurveyTreeItem.__table__.delete().where(filter))
self.session.touch()
datamanager.mark_changed(session)
return removed
class Risk(SurveyTreeItem):
"""Answer to risk."""
__tablename__ = "risk"
__mapper_args__ = dict(polymorphic_identity="risk")
sql_risk_id = schema.Column(
"id",
types.Integer(),
schema.ForeignKey(SurveyTreeItem.id,
onupdate="CASCADE",
ondelete="CASCADE"),
primary_key=True,
)
risk_id = schema.Column(types.String(16), nullable=True)
risk_type = schema.Column(Enum(["risk", "policy", "top5"]),
default="risk",
nullable=False,
index=True)
#: Skip-evaluation flag. This is only used to indicate if the sector
#: set the evaluation method to `fixed`, not for policy behaviour
#: such as not evaluation top-5 risks. That policy behaviour is
#: handled via the question_filter on client views so it can be modified
#: in custom deployments.
skip_evaluation = schema.Column(types.Boolean(),
default=False,
nullable=False)
is_custom_risk = schema.Column(types.Boolean(),
default=False,
nullable=False)
identification = schema.Column(Enum([None, "yes", "no", "n/a"]))
frequency = schema.Column(types.Integer())
effect = schema.Column(types.Integer())
probability = schema.Column(types.Integer())
priority = schema.Column(Enum([None, "low", "medium", "high"]))
comment = schema.Column(types.UnicodeText())
existing_measures = schema.Column(types.UnicodeText())
training_notes = schema.Column(types.UnicodeText())
custom_description = schema.Column(types.UnicodeText())
image_data = schema.Column(types.LargeBinary())
image_data_scaled = schema.Column(types.LargeBinary())
image_filename = schema.Column(types.UnicodeText())
@memoize
def measures_of_type(self, plan_type):
query = (Session.query(ActionPlan).filter(
sql.and_(ActionPlan.risk_id == self.id),
ActionPlan.plan_type == plan_type,
).order_by(ActionPlan.id))
return query.all()
@property
def standard_measures(self):
return self.measures_of_type("measure_standard")
@property
def custom_measures(self):
return self.measures_of_type("measure_custom")
@property
def in_place_standard_measures(self):
return self.measures_of_type("in_place_standard")
@property
def in_place_custom_measures(self):
return self.measures_of_type("in_place_custom")