def RGStatistik(az, bestaet, unfoid): datum = str(strftime("%d.%m.%Y", localtime())) upd = z1vrs1aa.update().where(and_(z1vrs1aa.c.az == az)).values( bestaet=bestaet, am=datum, unfoid=unfoid) session = Session() session.execute(upd) mark_changed(session)
def render(self): dbtable_surveys = 'statistics_surveys' info_surveys = self.getSurveysInfo() # write to db session = Session() session.execute('''DELETE FROM %s;''' % dbtable_surveys) def clean(value): if isinstance(value, basestring): return safe_unicode(value).strip().encode('utf-8') return value def pg_format(value): if value is None: return 'NULL' if isinstance(value, datetime): return "TIMESTAMP '%s'" % value.isoformat() return "'%s'" % value for line in info_surveys: insert = '''INSERT INTO %s VALUES %s;''' % \ (dbtable_surveys, '(%s)' % ', '.join(map(pg_format, map(clean, line)))) session.execute(insert) datamanager.mark_changed(session) transaction.get().commit() from pprint import pformat return "Written:\n" + pformat(info_surveys)
def render(self): log.info('Called: write_statistics') dbtable_surveys = 'statistics_surveys' info_surveys = self.getSurveysInfo() # write to db session = Session() session.execute('''DELETE FROM %s;''' % dbtable_surveys) def clean(value): if isinstance(value, basestring): return safe_unicode(value).strip().encode('utf-8') return value def pg_format(value): if value is None: return 'NULL' if isinstance(value, datetime): return "TIMESTAMP '%s'" % value.isoformat() return "'%s'" % value for line in info_surveys: insert = '''INSERT INTO %s VALUES %s;''' % \ (dbtable_surveys, '(%s)' % ', '.join(map(pg_format, map(clean, line)))) session.execute(insert) datamanager.mark_changed(session) transaction.get().commit() log.info('Exported statistics on {0} surveys to the DB.'.format( len(info_surveys))) from pprint import pformat return "Written:\n" + pformat(info_surveys)
def enable_longer_zodb_paths(context): session = Session() session.execute( "ALTER TABLE %s ALTER COLUMN zodb_path TYPE varchar(512);" % model.SurveyTreeItem.__table__.name ) datamanager.mark_changed(session)
def render(self): log.info("Called: write_statistics") dbtable_surveys = "statistics_surveys" info_surveys = self.getSurveysInfo() # write to db session = Session() session.execute("""DELETE FROM %s;""" % dbtable_surveys) def clean(value): if isinstance(value, basestring): return safe_unicode(value).strip().encode("utf-8") return value def pg_format(value): if value is None: return "NULL" if isinstance(value, datetime): return "TIMESTAMP '%s'" % value.isoformat() return "'%s'" % value for line in info_surveys: insert = """INSERT INTO %s VALUES %s;""" % ( dbtable_surveys, "(%s)" % ", ".join(map(pg_format, map(clean, line))), ) session.execute(insert) datamanager.mark_changed(session) transaction.get().commit() log.info("Exported statistics on {0} surveys to the DB.".format( len(info_surveys))) from pprint import pformat return "Written:\n" + pformat(info_surveys)
def getGrundDaten(self): if os.environ.get('ADHOC_TEST') == "True": from ukh.adhoc.lib.testdata import gd return gd d1 = select([c1unf1aa, c1prs1aa], and_(c1prs1aa.c.prsoid == c1unf1aa.c.unfprs, c1unf1aa.c.unfaz == self.az)) session = Session() daten1 = session.execute(d1).fetchall() d2 = select([avika1aa], and_(avika1aa.c.ikkl == str(daten1[0]['prsikn'])[0:3], avika1aa.c.ikbs == str(daten1[0]['prsikn'])[3:10])) prsdaten = session.execute(d2).fetchall() datensatz = {} datensatz.update(dict(prsdaten[0])) datensatz.update(dict(daten1[0])) if datensatz['ikanr'] == 1: datensatz['ikanr'] = u'Frau' else: datensatz['ikanr'] = u'Herr' nd = {} for k, v in datensatz.items(): if isinstance(v, basestring): nd[k] = v else: nd[k] = str(v) return datensatz
def add_actionplan_reference(context): session = Session() inspector = Inspector.from_engine(session.bind) columns = [c['name'] for c in inspector.get_columns(ActionPlan.__table__.name)] if 'reference' not in columns: log.info('Adding reference column for action plans') session.execute('ALTER TABLE action_plan ADD COLUMN reference TEXT')
def test_not_nullable(self): import mock from z3c.saconfig import Session session = Session() session.execute('CREATE TABLE account (password TEXT)') session.execute = mock.Mock() self.allow_empty_password() self.assertTrue(not session.execute.called)
def test_column_not_present(self): import mock from z3c.saconfig import Session session = Session() session.execute('CREATE TABLE risk (foo INT)') session.execute = mock.Mock() self.add_skip_evaluation_to_model() self.assertTrue(session.execute.called)
def test_not_nullable(self): import mock from z3c.saconfig import Session session = Session() session.execute('CREATE TABLE action_plan (reference TEXT)') session.execute = mock.Mock() self.add_actionplan_reference() self.assertTrue(not session.execute.called)
def drop_constraint_no_duplicates_in_tree(context): session = Session() if TableExists(session, "tree"): session.execute("ALTER TABLE tree DROP CONSTRAINT no_duplicates") model.metadata.create_all(session.bind, checkfirst=True) datamanager.mark_changed(session) transaction.get().commit() log.info("Removed the constraint `no_duplicates` from table tree.")
def increase_sessions_path_column(context): session = Session() if TableExists(session, "session"): session.execute("ALTER TABLE session ALTER COLUMN zodb_path TYPE varchar(512)") model.metadata.create_all(session.bind, checkfirst=True) datamanager.mark_changed(session) transaction.get().commit() log.info("Increased the size of column zodb_path in table session.")
def enable_custom_risks_on_all_modules(context): """ """ if not api.portal.get_registry_record("euphorie.allow_user_defined_risks"): log.warning( "Custom risks are not enabled. Set 'allow_user_defined_risks' to " "true in euphorie.ini for enabling them." ) return portal = api.portal.get() client = portal.client count = 0 for country in client.objectValues(): if IClientCountry.providedBy(country): for sector in country.objectValues(): if IClientSector.providedBy(sector): for survey in sector.objectValues(): try: is_new = EnableCustomRisks(survey) count += 1 custom = getattr(survey, "custom-risks", None) if custom: custom.title = _( "title_other_risks", default="Added risks (by you)", ) custom.description = _( "description_other_risks", default="In case you have identified risks not included in " # noqa: E501 "the tool, you are able to add them now:", ) custom.question = _( "question_other_risks", default="<p>Would you now like to add your own defined risks " # noqa: E501 "to this tool?</p><p><strong>Important:</strong> In " # noqa: E501 "order to avoid duplicating risks, we strongly recommend you " # noqa: E501 "to go first through all the previous modules, if you have not " # noqa: E501 "done it yet.</p><p>If you don't need to add risks, please select 'No.'</p>", # noqa: E501 ) if is_new: survey.published = ( survey.id, survey.title, datetime.datetime.now(), ) except Exception as e: log.error( "Could not enable custom risks for module. %s" % e ) log.info("All %d published surveys can now have custom risks." % count) session = Session() if TableExists(session, "tree"): session.execute( "UPDATE tree SET title = 'title_other_risks' WHERE zodb_path ='custom-risks'" # noqa: E501 ) model.metadata.create_all(session.bind, checkfirst=True) datamanager.mark_changed(session) transaction.get().commit() log.info("Set correct title on all exisiting sessions for custom risks module.")
def test_nullable(self): import mock from z3c.saconfig import Session session = Session() session.execute('CREATE TABLE account (password TEXT NOT NULL)') session.execute = mock.Mock() self.allow_empty_password() session.execute.assert_called_once_with( 'ALTER TABLE account ALTER COLUMN password DROP NOT NULL')
def add_actionplan_reference(context): session = Session() inspector = Inspector.from_engine(session.bind) columns = [ c['name'] for c in inspector.get_columns(ActionPlan.__table__.name) ] if 'reference' not in columns: log.info('Adding reference column for action plans') session.execute('ALTER TABLE action_plan ADD COLUMN reference TEXT')
def add_od_version_column(context): session = Session() if column_exists(session, 'od_link', 'version'): return transaction.get().commit() session.execute('ALTER TABLE od_link ADD COLUMN version INT DEFAULT 0 NOT NULL') datamanager.mark_changed(session) transaction.get().commit() log.info("Added new column 'version' to table 'od_link'")
def test_nullable(self): import mock from z3c.saconfig import Session session = Session() session.execute('CREATE TABLE action_plan (foo INT)') session.execute = mock.Mock() self.add_actionplan_reference() session.execute.assert_called_once_with( 'ALTER TABLE action_plan ADD COLUMN reference TEXT')
def increase_sessions_path_column(context): session = Session() if TableExists(session, "session"): session.execute( "ALTER TABLE session ALTER COLUMN zodb_path TYPE varchar(512)") model.metadata.create_all(session.bind, checkfirst=True) datamanager.mark_changed(session) transaction.get().commit() log.info("Increased the size of column zodb_path in table session.")
def alter_time_column(context): session = Session() if TableExists(session, "statistics_login"): session.execute( "ALTER TABLE statistics_login ALTER COLUMN time SET DEFAULT CURRENT_TIMESTAMP") model.metadata.create_all(session.bind, checkfirst=True) datamanager.mark_changed(session) transaction.get().commit() log.info("Changed default for column 'time' to current timestamp")
def drop_constraint_no_duplicates_in_tree(context): session = Session() if TableExists(session, "tree"): session.execute( "ALTER TABLE tree DROP CONSTRAINT no_duplicates") model.metadata.create_all(session.bind, checkfirst=True) datamanager.mark_changed(session) transaction.get().commit() log.info("Removed the constraint `no_duplicates` from table tree.")
def alter_time_column(context): session = Session() if TableExists(session, "statistics_login"): session.execute( "ALTER TABLE statistics_login ALTER COLUMN time SET DEFAULT CURRENT_TIMESTAMP" ) model.metadata.create_all(session.bind, checkfirst=True) datamanager.mark_changed(session) transaction.get().commit() log.info("Changed default for column 'time' to current timestamp")
def add_wp_column_to_company(context): session = Session() if TableExists(session, "company"): session.execute( "ALTER TABLE company ADD workers_participated bool DEFAULT NULL") model.metadata.create_all(session.bind, checkfirst=True) datamanager.mark_changed(session) transaction.get().commit() log.info("Added new column 'workers_participated' to table 'company'")
def allow_empty_password(context): session = Session() inspector = Inspector.from_engine(session.bind) columns = inspector.get_columns(Account.__table__.name) password = [c for c in columns if c['name'] == 'password'][0] if not password['nullable']: log.info('Dropping NOT NULL constraint for account.password') session.execute( 'ALTER TABLE account ALTER COLUMN password DROP NOT NULL') datamanager.mark_changed(session)
def add_od_version_column(context): session = Session() if column_exists(session, "od_link", "version"): return transaction.get().commit() session.execute( "ALTER TABLE od_link ADD COLUMN version INT DEFAULT 0 NOT NULL") datamanager.mark_changed(session) transaction.get().commit() log.info("Added new column 'version' to table 'od_link'")
def add_column_for_existing_measures(context): session = Session() inspector = Inspector.from_engine(session.bind) columns = [ c['name'] for c in inspector.get_columns(model.Risk.__table__.name) ] if 'existing_measures' not in columns: log.info('Adding existing_measures column for risks') session.execute("ALTER TABLE %s ADD existing_measures TEXT" % model.Risk.__table__.name) datamanager.mark_changed(session)
def add_has_description_column(context): session = Session() if ColumnExists(session, 'tree', 'has_description'): return transaction.get().commit() session.execute("ALTER TABLE tree ADD has_description bool DEFAULT 'f'") model.metadata.create_all(session.bind, checkfirst=True) datamanager.mark_changed(session) transaction.get().commit() log.info("Added new column 'has_description' to table 'tree'")
def add_skip_evaluation_to_model(context): session = Session() inspector = Inspector.from_engine(session.bind) columns = [c['name'] for c in inspector.get_columns(model.Risk.__table__.name)] if 'skip_evaluation' not in columns: log.info('Adding skip_evaluation column for risks') session.execute( "ALTER TABLE %s ADD skip_evaluation BOOL DEFAULT 'f' NOT NULL" % model.Risk.__table__.name) datamanager.mark_changed(session)
def add_column_for_training_notes(context): session = Session() inspector = Inspector.from_engine(session.bind) columns = [ c["name"] for c in inspector.get_columns(model.Risk.__table__.name) ] if "training_notes" not in columns: log.info("Adding training_notes column for risks") session.execute("ALTER TABLE %s ADD training_notes TEXT" % model.Risk.__table__.name) datamanager.mark_changed(session)
def add_column_for_custom_risks(context): session = Session() inspector = Inspector.from_engine(session.bind) columns = [c['name'] for c in inspector.get_columns(model.Risk.__table__.name)] if 'is_custom_risk' not in columns: log.info('Adding is_custom_risk column for risks') session.execute( "ALTER TABLE %s ADD is_custom_risk BOOL NOT NULL DEFAULT FALSE" % model.Risk.__table__.name) datamanager.mark_changed(session)
def add_columns_to_company_survey(context): session = Session() inspector = Inspector.from_engine(session.bind) columns = [c["name"] for c in inspector.get_columns(model.Company.__table__.name)] if "needs_met" not in columns: log.info("Adding needs_met column for company") session.execute("ALTER TABLE %s ADD needs_met BOOL " % model.Company.__table__.name) datamanager.mark_changed(session) if "recommend_tool" not in columns: log.info("Adding recommend_tool column for company") session.execute("ALTER TABLE %s ADD recommend_tool BOOL " % model.Company.__table__.name) datamanager.mark_changed(session)
def add_column_for_custom_risks(context): session = Session() inspector = Inspector.from_engine(session.bind) columns = [ c['name'] for c in inspector.get_columns(model.Risk.__table__.name) ] if 'is_custom_risk' not in columns: log.info('Adding is_custom_risk column for risks') session.execute( "ALTER TABLE %s ADD is_custom_risk BOOL NOT NULL DEFAULT FALSE" % model.Risk.__table__.name) datamanager.mark_changed(session)
def make_risk_id_column_nullable(context): """ Make the risk_id column of the Risk table nullable. This is so that the user can create custom risks. These risks don't have dexterity counterparts in the survey, so we don't have a value for risk_id. """ session = Session() inspector = Inspector.from_engine(session.bind) log.info('Making the risk_id column of Risk table nullable') session.execute("ALTER TABLE %s ALTER COLUMN risk_id DROP NOT NULL;" % model.Risk.__table__.name) datamanager.mark_changed(session)
def PRStatistik(unfaz, dok_typ, edt_typ): datum = str(strftime("%d.%m.%Y", localtime())) datum1 = str(strftime("%Y%m%d", localtime())) sql = z1vrs2aa.insert( dict(unfaz=unfaz, datum=datum, datum1=datum1, dok_typ=dok_typ, edt_typ=edt_typ)) session = Session() session.execute(sql) mark_changed(session)
def removeChildren(self): session = Session() if self.path: filter = sql.and_(SurveyTreeItem.session_id == self.session_id, SurveyTreeItem.path.like(self.path + "%"), SurveyTreeItem.id != self.id) else: filter = sql.and_(SurveyTreeItem.session_id == self.session_id, SurveyTreeItem.id != self.id) session.execute(SurveyTreeItem.__table__.delete().where(filter)) self.session.touch() datamanager.mark_changed(session)
def add_has_description_column(context): session = Session() if ColumnExists(session, 'tree', 'has_description'): return transaction.get().commit() session.execute( "ALTER TABLE tree ADD has_description bool DEFAULT 'f'") model.metadata.create_all(session.bind, checkfirst=True) datamanager.mark_changed(session) transaction.get().commit() log.info("Added new column 'has_description' to table 'tree'")
def updateAbsenteePercentage(context): from z3c.saconfig import Session from euphorie.deployment.upgrade.utils import ColumnType from zope.sqlalchemy import datamanager session=Session() if ColumnType(session, "dutch_company", "absentee_percentage")=="numeric(5,2)": return log.info("Changing type for dutch_company.absentee_percentage to NUMERIC(5,2)") session.execute("ALTER TABLE dutch_company ALTER COLUMN absentee_percentage TYPE NUMERIC(5,2);") datamanager.mark_changed(session)
def addTermsAndConditionsColumn(context): from z3c.saconfig import Session from euphorie.deployment.upgrade.utils import ColumnExists from zope.sqlalchemy import datamanager import transaction session = Session() if ColumnExists(session, "user", "tc_approved"): return log.info("Adding tc_approved column to account table") session.execute("ALTER TABLE account ADD COLUMN tc_approved INT") datamanager.mark_changed(session) transaction.get().commit()
def make_risk_id_column_nullable(context): """ Make the risk_id column of the Risk table nullable. This is so that the user can create custom risks. These risks don't have dexterity counterparts in the survey, so we don't have a value for risk_id. """ session = Session() inspector = Inspector.from_engine(session.bind) log.info('Making the risk_id column of Risk table nullable') session.execute( "ALTER TABLE %s ALTER COLUMN risk_id DROP NOT NULL;" % model.Risk.__table__.name ) datamanager.mark_changed(session)
def add_columns_to_company_survey(context): session = Session() inspector = Inspector.from_engine(session.bind) columns = [ c['name'] for c in inspector.get_columns(model.Company.__table__.name) ] if 'needs_met' not in columns: log.info('Adding needs_met column for company') session.execute("ALTER TABLE %s ADD needs_met BOOL " % model.Company.__table__.name) datamanager.mark_changed(session) if 'recommend_tool' not in columns: log.info('Adding recommend_tool column for company') session.execute("ALTER TABLE %s ADD recommend_tool BOOL " % model.Company.__table__.name) datamanager.mark_changed(session)
def getStatus(self): # Note: Optional modules with a yes-answer are not distinguishable # from non-optional modules, and ignored. session_id = SessionManager.id query = self.query % dict(sessionid=session_id) session = Session() result = session.execute(query).fetchall() modules = {} base_url = "%s/identification" % self.request.survey.absolute_url() for row in result: module = modules.setdefault(row.module, dict()) if "url" not in module: module["url"] = "%s/%s" % (base_url, int(row.module)) module["path"] = row.module if row.status != "ignore": module["total"] = module.get("total", 0) + row.count module[row.status] = {'count': row.count} titles = dict(session.query(model.Module.path, model.Module.title) .filter(model.Module.session_id == session_id) .filter(model.Module.path.in_(modules.keys()))) for module in modules.values(): module["title"] = titles[module["path"]] for status in ["postponed", "ok", "risk"]: if status in module: module[status]["width"] = int(570 * (float(module[status]["count"]) / module["total"])) self.status = modules.values() self.status.sort(key=lambda m: m["path"])
def updateAbsenteePercentage(context): from euphorie.deployment.upgrade.utils import ColumnType from z3c.saconfig import Session from zope.sqlalchemy import datamanager session = Session() if ColumnType(session, "dutch_company", "absentee_percentage") == "numeric(5,2)": return log.info( "Changing type for dutch_company.absentee_percentage to NUMERIC(5,2)") session.execute( "ALTER TABLE dutch_company ALTER COLUMN absentee_percentage TYPE NUMERIC(5,2);" ) datamanager.mark_changed(session)
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
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
def add_column_to_account(context): """ Adds a new column to the Account table which indicates whether the account in question is a guest account, a converted guest account or neither. """ session = Session() if ColumnExists(session, "account", "account_type"): log.info("account_type column already exists in Account table!") return log.info('Adding account_type column to Account table') q = "ALTER TABLE account ADD COLUMN account_type CHARACTER varying(16)"; try: session.execute(q) except InternalError, e: # There might be previous SQL queries which failed due to the # account_type column not yet being in the Account table. For example, # the authenticate method in authentication.py does such a query. session.rollback() transaction.commit() session.execute(q)
def add_column_to_account(context): """ Adds a new column to the Account table which indicates whether the account in question is a guest account, a converted guest account or neither. """ session = Session() if ColumnExists(session, "account", "account_type"): log.info("account_type column already exists in Account table!") return log.info('Adding account_type column to Account table') q = "ALTER TABLE account ADD COLUMN account_type CHARACTER varying(16)" try: session.execute(q) except InternalError, e: # There might be previous SQL queries which failed due to the # account_type column not yet being in the Account table. For example, # the authenticate method in authentication.py does such a query. session.rollback() transaction.commit() session.execute(q)
def migrateCompanyTable(context): from z3c.saconfig import Session from euphorie.deployment.upgrade.utils import ColumnExists from euphorie.deployment.upgrade.utils import TableExists from euphorie.client import model from zope.sqlalchemy import datamanager import transaction session = Session() if ColumnExists(session, "company", "referer"): return if TableExists(session, "company"): log.info("Moving company table to dutch_company") session.execute("ALTER TABLE company RENAME TO dutch_company") session.execute( "ALTER SEQUENCE company_id_seq RENAME TO dutch_company_id_seq") session.execute( "ALTER INDEX ix_company_session_id RENAME TO " "ix_dutch_company_session_id") model.metadata.create_all(session.bind, checkfirst=True) datamanager.mark_changed(session) transaction.get().commit() log.info("Creating new company table")
def asPlainText(self, data={}): if 'dry-run' in data: yield "*** DRY RUN ***\n" total_reclaimed = 0 for (studentId, unclaimed) in Session.execute(""" SELECT a.studentId, SUM(coinsAwarded) - COALESCE((SELECT SUM(amount) FROM coinAward ca WHERE ca.studentId = a.studentId), 0) AS unclaimed FROM answer a, student s WHERE coinsAwarded > 0 AND a.studentId = s.studentId AND s.hostId = 1 AND a.timeEnd < CURDATE() - INTERVAL 2 YEAR AND a.studentId NOT IN (SELECT DISTINCT c.studentId FROM coinAward c WHERE c.awardTime >= CURDATE() - INTERVAL 2 YEAR) GROUP BY a.studentId HAVING unclaimed > 0 """): total_reclaimed += int(unclaimed) if 'dry-run' in data: txId = 'DRY_RUN' else: txId = coin.sendTransaction(EIAS_WALLET, unclaimed, message="Auto-reclaim of awards") # Worked, so update database Session.add(db.CoinAward( studentId=studentId, amount=int(unclaimed), walletId=EIAS_WALLET, txId=txId, awardTime=utcnow(), # NB: So it gets mocked in the tests )) Session.flush() yield "StudentId: %d Unclaimed: %d Transaction: %s\n" % ( studentId, unclaimed, txId, ) yield "Total: %d\n" % total_reclaimed
def getRisks(self, module_paths): """ Return a list of risk dicts for risks that belong to the modules with paths as specified in module_paths. """ session = Session() session_id = SessionManager.id # First, we need to compute the actual module paths, making sure that # skipped optional modules are excluded # This means top-level module paths like 001 or 001002 can be replaced # by several sub-modules paths like 001002, 001003 and 001002001 module_query = """ SELECT path FROM tree WHERE session_id={0} AND type='module' AND skip_children='f' and tree.path similar to '({1}%)' ORDER BY path """.format(session_id, "%|".join(module_paths)) module_res = session.execute(module_query).fetchall() def nodes(paths): paths = sorted(paths, reverse=True) ret = [] for elem in paths: if not [x for x in ret if x.startswith(elem)]: ret.append(elem) return ret # Here we make sure that only the longest paths of sub-modules # are used, but not the parents. Example # (001, 002, 001001, 001003) will be turned into # (001001, 001003, 002), since the parent 001 contains sub-modules, # and some of those might have been de-selected, like 001002 filtered_module_paths = nodes([x[0] for x in module_res]) child_node = orm.aliased(model.Risk) risks = session.query( model.Module.path, child_node.id, child_node.path, child_node.title, child_node.identification, child_node.priority, child_node.risk_type, child_node.zodb_path, child_node.is_custom_risk, child_node.postponed ).filter( sql.and_( model.Module.session_id == session_id, model.Module.path.in_(filtered_module_paths), sql.and_( child_node.session_id == model.Module.session_id, child_node.depth > model.Module.depth, child_node.path.like(model.Module.path + "%") ) ) ) def _module_path(path): # Due to the extended query above that replaces top-module paths # with sub-module paths (if present), we need to cut back the path # under which we store each risk back to the original top-level # module path for mp in module_paths: if path.startswith(mp): return mp return [{ 'module_path': _module_path(risk[0]), 'id': risk[1], 'path': risk[2], 'title': risk[3], 'identification': risk[4], 'priority': risk[5], 'risk_type': risk[6], 'zodb_path': risk[7], 'is_custom_risk': risk[8], 'postponed': risk[9], } for risk in risks]
def getModules(self): """ Return a list of dicts of all the top-level modules and locations belonging to this survey. """ session = Session() session_id = SessionManager.id base_url = "%s/identification" % self.request.survey.absolute_url() profile = extractProfile(self.request.survey, SessionManager.session) module_query = self.module_query( sessionid=session_id, optional_modules=len(profile) and "(%s)" % (','.join( ["'%s'" % k for k in profile.keys()])) or None ) module_res = session.execute(module_query).fetchall() modules_and_profiles = {} for row in module_res: if row[0] is not None: if row[0].find('profile') > 0: path = row[0][:3] modules_and_profiles[path] = 'profile' else: modules_and_profiles[row[0]] = '' module_paths = [ p[0] for p in session.execute(module_query).fetchall() if p[0] is not None] module_paths = modules_and_profiles.keys() module_paths = sorted(module_paths) parent_node = orm.aliased(model.Module) titles = dict(session.query(model.Module.path, model.Module.title) .filter(model.Module.session_id == session_id) .filter(model.Module.path.in_(module_paths))) location_titles = dict(session.query( model.Module.path, parent_node.title ).filter( model.Module.session_id == session_id).filter( model.Module.path.in_(module_paths)).filter( sql.and_( parent_node.session_id == session_id, parent_node.depth < model.Module.depth, model.Module.path.like(parent_node.path + "%") ) )) modules = {} toc = {} title_custom_risks = utils.get_translated_custom_risks_title(self.request) for path in module_paths: number = ".".join(self.slicePath(path)) # top-level module, always include it in the toc if len(path) == 3: title = titles[path] if title == 'title_other_risks': title = title_custom_risks toc[path] = { 'path': path, 'title': title, 'locations': [], 'number': number, } # If this is a profile (aka container for locations), skip # adding to the list of modules if modules_and_profiles[path] == 'profile': continue # sub-module (location) or location container else: if path in location_titles: title = u"{0} - {1}".format(location_titles[path], titles[path]) toc[path[:3]]['locations'].append({ 'path': path, 'title': titles[path], 'number': number, }) else: log.warning( "Status: found a path for a submodule {0} for which " "there's no location title.".format(path)) continue modules[path] = { 'path': path, 'title': title, 'url': '%s/%s' % (base_url, '/'.join(self.slicePath(path))), 'todo': 0, 'ok': 0, 'postponed': 0, 'risk_with_measures': 0, 'risk_without_measures': 0, 'number': number, } self.tocdata = toc return modules
def copySessionData(self, other): """Copy all user data from another session to this one. """ session = Session() # Copy all tree data to the new session (skip_children and postponed) old_tree = orm.aliased(SurveyTreeItem, name='old_tree') in_old_tree = sql.and_( old_tree.session_id == other.id, SurveyTreeItem.zodb_path == old_tree.zodb_path, SurveyTreeItem.profile_index == old_tree.profile_index) skip_children = sql.select([old_tree.skip_children], in_old_tree) postponed = sql.select([old_tree.postponed], in_old_tree) new_items = session.query(SurveyTreeItem)\ .filter(SurveyTreeItem.session == self)\ .filter(sql.exists( sql.select([old_tree.id]).where(in_old_tree))) new_items.update({'skip_children': skip_children, 'postponed': postponed}, synchronize_session=False) # Mandatory modules must have skip_children=False. It's possible that # the module was optional with skip_children=True and now after the # update it's mandatory. So we must check and correct. # In case a risk was marked as "always present", be sure its # identification gets set to 'no' preset_to_no = [] survey = getSite()['client'].restrictedTraverse(self.zodb_path) for item in new_items.all(): if item.type == 'risk': if item.identification == u'no': preset_to_no.append(item.risk_id) elif item.type == 'module': module = survey.restrictedTraverse(item.zodb_path.split("/")) if not module.optional: item.skip_children = False # Copy all risk data to the new session # This triggers a "Only update via a single table query is currently supported" # error with SQLAlchemy 0.6.6 # old_risk = orm.aliased(Risk.__table__, name='old_risk') # is_old_risk = sql.and_(in_old_tree, old_tree.id == old_risk.id) # identification = sql.select([old_risk.identification], is_old_risk) # new_risks = session.query(Risk)\ # .filter(Risk.session == self)\ # .filter(sql.exists( # sql.select([SurveyTreeItem.id]).where(sql.and_( # SurveyTreeItem.id == Risk.id, # sql.exists([old_tree.id]).where(sql.and_( # in_old_tree, old_tree.type == 'risk')))))) # new_risks.update({'identification': identification}, # synchronize_session=False) skip_preset_to_no_clause = "" if len(preset_to_no): skip_preset_to_no_clause = "old_risk.risk_id not in %s AND" % ( str([str(x) for x in preset_to_no]).replace('[', '(').replace(']', ')') ) statement = """\ UPDATE RISK SET identification = old_risk.identification, frequency = old_risk.frequency, effect = old_risk.effect, probability = old_risk.probability, priority = old_risk.priority, comment = old_risk.comment FROM risk AS old_risk JOIN tree AS old_tree ON old_tree.id=old_risk.id, tree WHERE tree.id=risk.id AND %(skip_preset_to_no_clause)s tree.session_id=%(new_sessionid)s AND old_tree.session_id=%(old_sessionid)s AND old_tree.zodb_path=tree.zodb_path AND old_tree.profile_index=tree.profile_index; """ % dict( old_sessionid=other.id, new_sessionid=self.id, skip_preset_to_no_clause=skip_preset_to_no_clause) session.execute(statement) statement = """\ INSERT INTO action_plan (risk_id, action_plan, prevention_plan, requirements, responsible, budget, planning_start, planning_end) SELECT new_tree.id, action_plan.action_plan, action_plan.prevention_plan, action_plan.requirements, action_plan.responsible, action_plan.budget, action_plan.planning_start, action_plan.planning_end FROM action_plan JOIN risk ON action_plan.risk_id=risk.id JOIN tree ON tree.id=risk.id, tree AS new_tree WHERE tree.session_id=%(old_sessionid)d AND new_tree.session_id=%(new_sessionid)d AND tree.zodb_path=new_tree.zodb_path AND tree.profile_index=new_tree.profile_index; """ % {'old_sessionid': other.id, 'new_sessionid': self.id} session.execute(statement) session.query(Company)\ .filter(Company.session == other)\ .update({'session_id': self.id}, synchronize_session=False)