Xray
daily rollups 1h 6h 24h 48h 3d 5d 7d 14d 30d ‹
Last 5 days▾
Ends now. Anything up to this cluster's 30-day retention.
Start and end times
Cancel
Interpreted in UTC (change it at the right of this bar). Earliest available: 2026-10-21 01:29:17.
› →|
updated 2d ago
Sample data. This database was generated by scripts/seed_dev_db.py — every service, deploy, person and figure on this page is synthetic. Names are sample.*@seed.invalid and image tags are prefixed SEED-. Nothing here happened.

Query trends 80

stored 2d ago top 80 of 1,454

Neo4j query shapes on test26 over the last 5d, grouped by plan hash. Click a column to sort; p95 drift (late-half ÷ early-half of the window) ranks the queries getting slower.

Columns
Views
Export CSV
Query shape Count Avg p95 ▾ Max p95 drift
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=Study policies=study-config-edit MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <>…
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=Study policies=study-config-edit MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <> ? AND a.delpending = false AND o.tildaid = a.orgId AND x.name IN $lists.roles_0 WITH DISTINCT u, o, a, c MATCH (a)<-[:STUDY_TO_ACTOR]-(st:Study) WHERE EXISTS { (st)-[:STUDY_TO_ORG]->(o) } AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) WITH * WHERE st.tildaid = c.id RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION WITH u, o, c // scope=Study policies=study-config-edit MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_1 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) WITH * WHERE st.tildaid = c.id RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION WITH u, o, c // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANIZATION_TO_ACTOR]->(a) WHERE a.state <> ? AND a.delpending = false AND x.name IN $lists.roles_2 MATCH (o)<-[:STUDY_TO_ORG]-(st:Study {tildaid:c.id}) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient UNION WITH u, o, c // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_3 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) MATCH (o)<-[:STUDY_TO_ORG]-(st:Study {tildaid:c.id}) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient } RETURN checkIdx, User, Organization, Study, Site, Patient
enrollsvc
1 2.21 s 4.85 s 2.21 s flat
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIO…
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIONAL MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o), (a)-[:APPOINTED_AS]->(r:Role) WHERE not r.name = ? AND coalesce(a.state, ?) <> ? return r{.*} UNION with u, o // Delegated engagements in o. Only the delegated roles cross over — // the TildaScale Actor?s list. MATCH (u)<-[:FOR_USER]-(da:Actor{delpending:false})<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study{delpending:false})-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(r:Role)<-[:APPOINTED_AS]-(sa:Actor{delpending:false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(r.tags, ?), ?) return distinct r{.*} } WITH u, o, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles, ANY(n IN COLLECT(r.external) WHERE n = true) as external, ANY(n IN COLLECT(r.name) WHERE n = ?) as isPatient, ALL(n IN COLLECT(r.piiAccess) WHERE n = false) as hidePhi WITH u, o, {organization:o{.*}, external: external, isPatient: isPatient, hidePhi: hidePhi, roles: roles, displayRoles: displayRoles } as context CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o) WHERE coalesce(a.state, ?) <> ? OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles WITH CASE when not si is null then {siteId: si.tildaid, roles: roles, displayRoles: displayRoles} end as si return collect(si) as sites, apoc.coll.flatten(collect(si.roles)) as siteRoles } CALL {WITH u, o // WITH DISTINCT is a required pipeline barrier (see the studyMembership note // in scope.go): without it (u)<-[:FOR_USER]-(a) re-expands once per study in // the org — ?.0M -> 32k db hits. Splitting the MATCH without DISTINCT does // nothing; the barrier is what forbids the bad join order. Not applied to the // sites or org-roles subqueries, where it measured neutral or worse. // No longer OPTIONAL: the subquery ends in an ungrouped aggregation, so zero // matches still yield one row with empty collections. CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false, orgId:o.tildaid}) WHERE coalesce(a.state, ?) <> ? WITH DISTINCT u, o, a MATCH (a)<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false})-[:STUDY_TO_ORG]->(o) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) RETURN s, a, r UNION // Delegated engagement Actors: orgId is TildaScale? roles. WITH s, r order by r.displayName WITH s, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles // Ordered so the studies array is a stable sequence for clients; the pipeline // that replaced the old (u,o,s,a) grouping otherwise leaves it plan-dependent. WITH s, CASE when not s is null then {studyId: s.tildaid, roles: roles, displayRoles: displayRoles, srcOrgId: s.srcorgid} end as st ORDER BY s.name, s.tildaid return collect(st) as studies, apoc.coll.flatten(collect(st.roles)) as studyRoles } with distinct context, studies, apoc.coll.toSet(apoc.coll.flatten(collect(studyRoles))) as allStudyRoles, apoc.coll.toSet(apoc.coll.flatten(collect(siteRoles))) as siRoles with context, studies, apoc.coll.toSet(apoc.coll.flatten([context.roles, allStudyRoles, siRoles])) as allRoles return context{.*, studies: studies, allRoles: allRoles}
dashql
1 2.94 s 4.85 s 2.94 s flat
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIO…
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIONAL MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o), (a)-[:APPOINTED_AS]->(r:Role) WHERE not r.name = ? AND coalesce(a.state, ?) <> ? return r{.*} UNION with u, o // Delegated engagements in o. Only the delegated roles cross over — // the TildaScale Actor?s list. MATCH (u)<-[:FOR_USER]-(da:Actor{delpending:false})<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study{delpending:false})-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(r:Role)<-[:APPOINTED_AS]-(sa:Actor{delpending:false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(r.tags, ?), ?) return distinct r{.*} } WITH u, o, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles, ANY(n IN COLLECT(r.external) WHERE n = true) as external, ANY(n IN COLLECT(r.name) WHERE n = ?) as isPatient, ALL(n IN COLLECT(r.piiAccess) WHERE n = false) as hidePhi WITH u, o, {organization:o{.*}, external: external, isPatient: isPatient, hidePhi: hidePhi, roles: roles, displayRoles: displayRoles } as context CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o) WHERE coalesce(a.state, ?) <> ? OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles WITH CASE when not si is null then {siteId: si.tildaid, roles: roles, displayRoles: displayRoles} end as si return collect(si) as sites, apoc.coll.flatten(collect(si.roles)) as siteRoles } CALL {WITH u, o // WITH DISTINCT is a required pipeline barrier (see the studyMembership note // in scope.go): without it (u)<-[:FOR_USER]-(a) re-expands once per study in // the org — ?.0M -> 32k db hits. Splitting the MATCH without DISTINCT does // nothing; the barrier is what forbids the bad join order. Not applied to the // sites or org-roles subqueries, where it measured neutral or worse. // No longer OPTIONAL: the subquery ends in an ungrouped aggregation, so zero // matches still yield one row with empty collections. CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false, orgId:o.tildaid}) WHERE coalesce(a.state, ?) <> ? WITH DISTINCT u, o, a MATCH (a)<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false})-[:STUDY_TO_ORG]->(o) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) RETURN s, a, r UNION // Delegated engagement Actors: orgId is TildaScale? roles. WITH s, r order by r.displayName WITH s, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles // Ordered so the studies array is a stable sequence for clients; the pipeline // that replaced the old (u,o,s,a) grouping otherwise leaves it plan-dependent. WITH s, CASE when not s is null then {studyId: s.tildaid, roles: roles, displayRoles: displayRoles, srcOrgId: s.srcorgid} end as st ORDER BY s.name, s.tildaid return collect(st) as studies, apoc.coll.flatten(collect(st.roles)) as studyRoles } with distinct context, studies, apoc.coll.toSet(apoc.coll.flatten(collect(studyRoles))) as allStudyRoles, apoc.coll.toSet(apoc.coll.flatten(collect(siteRoles))) as siRoles with context, studies, apoc.coll.toSet(apoc.coll.flatten([context.roles, allStudyRoles, siRoles])) as allRoles return context{.*, studies: studies, allRoles: allRoles}
dashql
6 1.17 s 4.70 s 2.85 s 0.0× ↓
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIO…
MATCH (u:User{tildaid:$userId}), (o:Organization{delpending:false, alias:$orgAlias}) call { with u, o // An Inactive actor holds no roles (ENG-?): its roles would otherwise // still feed hidePhi, the role list and every policy match. OPTIONAL MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o), (a)-[:APPOINTED_AS]->(r:Role) WHERE not r.name = ? AND coalesce(a.state, ?) <> ? return r{.*} UNION with u, o // Delegated engagements in o. Only the delegated roles cross over — // the TildaScale Actor?s list. MATCH (u)<-[:FOR_USER]-(da:Actor{delpending:false})<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study{delpending:false})-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(r:Role)<-[:APPOINTED_AS]-(sa:Actor{delpending:false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization{alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(r.tags, ?), ?) return distinct r{.*} } WITH u, o, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles, ANY(n IN COLLECT(r.external) WHERE n = true) as external, ANY(n IN COLLECT(r.name) WHERE n = ?) as isPatient, ALL(n IN COLLECT(r.piiAccess) WHERE n = false) as hidePhi WITH u, o, {organization:o{.*}, external: external, isPatient: isPatient, hidePhi: hidePhi, roles: roles, displayRoles: displayRoles } as context CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o) WHERE coalesce(a.state, ?) <> ? OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles WITH CASE when not si is null then {siteId: si.tildaid, roles: roles, displayRoles: displayRoles} end as si return collect(si) as sites, apoc.coll.flatten(collect(si.roles)) as siteRoles } CALL {WITH u, o // WITH DISTINCT is a required pipeline barrier (see the studyMembership note // in scope.go): without it (u)<-[:FOR_USER]-(a) re-expands once per study in // the org — ?.0M -> 32k db hits. Splitting the MATCH without DISTINCT does // nothing; the barrier is what forbids the bad join order. Not applied to the // sites or org-roles subqueries, where it measured neutral or worse. // No longer OPTIONAL: the subquery ends in an ungrouped aggregation, so zero // matches still yield one row with empty collections. CALL {WITH u, o MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false, orgId:o.tildaid}) WHERE coalesce(a.state, ?) <> ? WITH DISTINCT u, o, a MATCH (a)<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false})-[:STUDY_TO_ORG]->(o) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) RETURN s, a, r UNION // Delegated engagement Actors: orgId is TildaScale? roles. WITH s, r order by r.displayName WITH s, collect(distinct r.name) as roles, collect(distinct case when r.listed = true then r.displayName end) as displayRoles // Ordered so the studies array is a stable sequence for clients; the pipeline // that replaced the old (u,o,s,a) grouping otherwise leaves it plan-dependent. WITH s, CASE when not s is null then {studyId: s.tildaid, roles: roles, displayRoles: displayRoles, srcOrgId: s.srcorgid} end as st ORDER BY s.name, s.tildaid return collect(st) as studies, apoc.coll.flatten(collect(st.roles)) as studyRoles } with distinct context, studies, apoc.coll.toSet(apoc.coll.flatten(collect(studyRoles))) as allStudyRoles, apoc.coll.toSet(apoc.coll.flatten(collect(siteRoles))) as siRoles with context, studies, apoc.coll.toSet(apoc.coll.flatten([context.roles, allStudyRoles, siRoles])) as allRoles return context{.*, studies: studies, allRoles: allRoles}
dashql
2 1.75 s 4.70 s 3.51 s flat
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather th…
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather than at each use: // the sites, countries and roles below all hang off it. OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$ctxOrgId0})-[:FOR_USER]->(:User{tildaid:$ctxUserId0}) WHERE studyActor.state <> ? // Collect sites where user has membership in this study OPTIONAL MATCH (studyActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WITH org, study, studyActor, collect(DISTINCT site.tildaid) as userSiteIds // Collect countries where user has membership in this study OPTIONAL MATCH (studyActor)<-[:STUDYCOUNTRYACTOR_TO_ACTOR]-(sca:StudyCountryActor) WITH org, study, studyActor, userSiteIds, collect(DISTINCT sca.country) as userCountryIds // Pre-collect policies for this user/role in this study OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study) WHERE EXISTS { MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) } OR (studyActor IS NOT NULL AND EXISTS { MATCH (policy)-[:POLICY_FOR_ROLE]->(role:Role)<-[:APPOINTED_AS]-(studyActor) }) WITH study, studyActor, userSiteIds, userCountryIds, collect(policy) as policies WITH {study:study, studyActor:studyActor, userSiteIds:userSiteIds, userCountryIds:userCountryIds, policies:policies} AS st WITH collect(distinct st) as studies // studyById lets each document row look its context up by study id instead of scanning // the whole list. Entries whose study did not resolve share the ? key; they all carry // the same empty context, so which one wins does not matter. The node-bearing maps go no // further than this subquery; studyIdsInScope needs no lookup, since an id with no Study // node can never equal a matched study?Basic??ReadWrite?ReadOnly?ReadWrite?ReadWrite?ReadOnly?NoAccess??Inactive? ?Received?Quality Control?PHI Issues???/???/???/') ELSE ? END as childFolderPath WITH docObj, ct, child, st, head(collect(childFolderPath)) AS childFolderPath WITH child, CASE WHEN ct IS NULL THEN null ELSE { documentId: child.tildaid, studyId: COALESCE(child.studyId, ?), studyName: COALESCE(st.name, ?), folderPath: childFolderPath, kind: ct.kind, stale: ct.sourceVersionId <> docObj.primaryVersionId, version: COALESCE(child.versionName, toString(child.majorVersion)) } END as copiedToEntry RETURN collect(copiedToEntry) as copiedTo, count(DISTINCT child) as copiedToCount } CALL { return null as promotedTo } CALL { return null AS docReviewStatus, null AS hasImportantComment, null AS discrepancyCount } CALL { WITH docObj OPTIONAL MATCH (docObj)<-[:INCLUDES]-(currentFolder:Folder{folderType:?}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (docObj)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (docObj)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) WITH CASE WHEN stagingInfo IS NOT NULL THEN {artifactId: stagingInfo.folderId, subArtifactId: stagingInfo.subArtifactId, stagingInfoId: stagingInfo.tildaid} WHEN currentFolder.level = ? THEN {artifactId: currentFolder.tildaid, subArtifactId: COALESCE(currentSubArtifact.tildaid, ?), stagingInfoId: ?} ELSE {artifactId: ?, subArtifactId: ?, stagingInfoId: ?} END as tmfInfo WITH tmfInfo.artifactId as artifactId, tmfInfo.subArtifactId as subArtifactId, tmfInfo.stagingInfoId as stagingInfoId OPTIONAL MATCH (artifact:Folder{tildaid:artifactId})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{name:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(allSubArtifacts:SubArtifact) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:subArtifactId}) OPTIONAL MATCH (subArtifact)-[:HAS]->(subArtifactConstraints:FolderConstraints) OPTIONAL MATCH (artifact)-[:HAS]->(artifactConstraints:FolderConstraints) WITH artifactId as folderId, subArtifactId, stagingInfoId, CASE WHEN zone.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+ ? + zone.name, ?) ELSE ? END as zone, CASE WHEN section.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+?+apoc.text.lpad(toString(section.number), ?, ?)+ ? + section.name, ?) ELSE ? END as section, CASE WHEN artifact.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+?+apoc.text.lpad(toString(section.number), ?, ?)+?+apoc.text.lpad(toString(artifact.number), ?, ?)+ ? + artifact.name, ?) ELSE ? END as artifact, CASE WHEN subArtifact.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+?+apoc.text.lpad(toString(section.number), ?, ?)+?+apoc.text.lpad(toString(artifact.number), ?, ?)+?+apoc.text.lpad(toString(subArtifact.number), ?, ?)+ ? + subArtifact.name, ?) ELSE ? END as subArtifact, COALESCE(subArtifact.name, ?) as subArtifactName, COALESCE(subArtifactConstraints, artifactConstraints) as constraints, NOT(COUNT(allSubArtifacts) > ? AND subArtifactId = ?) as subArtifactValid OPTIONAL MATCH (constraints)-[:HAS_ATTRIBUTE_CONSTRAINT]->(dac:DocAttributeConstraint) WITH folderId, subArtifactId, stagingInfoId, zone, section, artifact, subArtifact, subArtifactName, subArtifactValid, constraints, COLLECT(DISTINCT CASE WHEN dac IS NOT NULL THEN {id: dac.tildaid, key: dac.key, order: dac.order, isMandatory: dac.isMandatory, description: dac.description} ELSE NULL END) as attributeConstraintsList WITH folderId, subArtifactId, stagingInfoId, zone, section, artifact, subArtifact, subArtifactName, subArtifactValid, CASE WHEN constraints IS NOT NULL THEN { id: constraints.tildaid, mandatory: constraints.mandatory, tmfLevel: constraints.tmfLevel, attributeConstraints: [attr IN attributeConstraintsList WHERE attr IS NOT NULL | attr] } ELSE null END as constraints WITH {folderId: folderId, subArtifactId: subArtifactId, stagingInfoId: stagingInfoId, zone: zone, section: section, artifact: artifact, subArtifact: subArtifact, subArtifactName: subArtifactName, subArtifactValid: subArtifactValid, path: apoc.text.join([x IN [zone, section, artifact] WHERE x <> ? | x], ?), constraints: constraints} as tmfCategorization RETURN CASE WHEN tmfCategorization.folderId=? THEN null ELSE tmfCategorization END as tmfCategorization } with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total return document, total
docsvc
3 1.25 s 4.55 s 3.28 s flat
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=study-config-edit,study-config-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) W…
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL (u, o, c) { // scope=Study policies=study-config-edit,study-config-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role) WHERE a.state <> ? AND a.delpending = false AND o.tildaid = a.orgId AND x.name IN $lists.roles_0 WITH DISTINCT u, o, a, c MATCH (a)<-[:STUDY_TO_ACTOR]-(st:Study) WHERE EXISTS { (st)-[:STUDY_TO_ORG]->(o) } AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION // scope=Study policies=study-config-edit,study-config-read MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:?}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_1 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) AND ( size(c.studyIds) = ? OR st.tildaid IN c.studyIds ) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, st AS Study, null AS Site, null AS Patient UNION // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANIZATION_TO_ACTOR]->(a) WHERE a.state <> ? AND a.delpending = false AND x.name IN $lists.roles_2 RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient UNION // scope=Organization policies=org-study-read MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:?}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_3 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient } RETURN checkIdx, User, Organization, Study, Site, Patient
chat-agent
4 530 ms 4.40 s 2.03 s flat
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather th…
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather than at each use: // the sites, countries and roles below all hang off it. OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$ctxOrgId0})-[:FOR_USER]->(:User{tildaid:$ctxUserId0}) WHERE studyActor.state <> ? // Collect sites where user has membership in this study OPTIONAL MATCH (studyActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WITH org, study, studyActor, collect(DISTINCT site.tildaid) as userSiteIds // Collect countries where user has membership in this study OPTIONAL MATCH (studyActor)<-[:STUDYCOUNTRYACTOR_TO_ACTOR]-(sca:StudyCountryActor) WITH org, study, studyActor, userSiteIds, collect(DISTINCT sca.country) as userCountryIds // Pre-collect policies for this user/role in this study OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study) WHERE EXISTS { MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) } OR (studyActor IS NOT NULL AND EXISTS { MATCH (policy)-[:POLICY_FOR_ROLE]->(role:Role)<-[:APPOINTED_AS]-(studyActor) }) WITH study, studyActor, userSiteIds, userCountryIds, collect(policy) as policies WITH {study:study, studyActor:studyActor, userSiteIds:userSiteIds, userCountryIds:userCountryIds, policies:policies} AS st WITH collect(distinct st) as studies // studyById lets each document row look its context up by study id instead of scanning // the whole list. Entries whose study did not resolve share the ? key; they all carry // the same empty context, so which one wins does not matter. The node-bearing maps go no // further than this subquery; studyIdsInScope needs no lookup, since an id with no Study // node can never equal a matched study?Basic??ReadWrite?ReadOnly?ReadWrite?ReadWrite?ReadOnly?NoAccess??Inactive? ?Received?Quality Control?PHI Issues???/???/???/') ELSE ? END as childFolderPath WITH docObj, ct, child, st, head(collect(childFolderPath)) AS childFolderPath WITH child, CASE WHEN ct IS NULL THEN null ELSE { documentId: child.tildaid, studyId: COALESCE(child.studyId, ?), studyName: COALESCE(st.name, ?), folderPath: childFolderPath, kind: ct.kind, stale: ct.sourceVersionId <> docObj.primaryVersionId, version: COALESCE(child.versionName, toString(child.majorVersion)) } END as copiedToEntry RETURN collect(copiedToEntry) as copiedTo, count(DISTINCT child) as copiedToCount } CALL { return null as promotedTo } CALL { return null AS docReviewStatus, null AS hasImportantComment, null AS discrepancyCount } CALL { return null as tmfCategorization } with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total return document, total
docsvc
2 1.76 s 1.95 s 1.82 s flat
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather th…
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather than at each use: // the sites, countries and roles below all hang off it. OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$ctxOrgId0})-[:FOR_USER]->(:User{tildaid:$ctxUserId0}) WHERE studyActor.state <> ? // Collect sites where user has membership in this study OPTIONAL MATCH (studyActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WITH org, study, studyActor, collect(DISTINCT site.tildaid) as userSiteIds // Collect countries where user has membership in this study OPTIONAL MATCH (studyActor)<-[:STUDYCOUNTRYACTOR_TO_ACTOR]-(sca:StudyCountryActor) WITH org, study, studyActor, userSiteIds, collect(DISTINCT sca.country) as userCountryIds // Pre-collect policies for this user/role in this study OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study) WHERE EXISTS { MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) } OR (studyActor IS NOT NULL AND EXISTS { MATCH (policy)-[:POLICY_FOR_ROLE]->(role:Role)<-[:APPOINTED_AS]-(studyActor) }) WITH study, studyActor, userSiteIds, userCountryIds, collect(policy) as policies WITH {study:study, studyActor:studyActor, userSiteIds:userSiteIds, userCountryIds:userCountryIds, policies:policies} AS st WITH collect(distinct st) as studies // studyById lets each document row look its context up by study id instead of scanning // the whole list. Entries whose study did not resolve share the ? key; they all carry // the same empty context, so which one wins does not matter. The node-bearing maps go no // further than this subquery; studyIdsInScope needs no lookup, since an id with no Study // node can never equal a matched study?Basic??ReadWrite?ReadOnly?s folder and ancestors (up to ? levels for TMF hierarchy) OPTIONAL MATCH (doc1)<-[:INCLUDES]-(docFolder:Folder) OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder) AS folderHierarchy // Check document ownership OPTIONAL MATCH (doc1)-[:UPLOADED_BY]->(uploader:User{tildaid:$policyUploaderId0}) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, folderHierarchy, uploader IS NOT NULL AS isOwnedByUser // Filter policies by: // ?. Folder: policy applies if folder is in hierarchy OR policy has no folder (study-wide). A folder-less // ownership policy is the role?Owned Documents?Non Owned Documents?Staging?Pre-Staging?Owned Documents?Non Owned Documents?My Sites Documents?Other Sites Documents?My Countries Documents?Other Countries Documents?Study Scope Documents?NoAccess?NoAccess?ReadOnly?ReadOnly?ReadWrite?ReadOnly?NoAccess??Inactive' AND sst.tildaid IN studyIdsInScope AND (doc1.siteId = ? OR (a)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)- [:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(:DslPlanDefinition{delpending:false,type:?})- [:PLAN_DEFINITION_TO_SITE]-(:Site{tildaid:doc1.siteId})) RETURN doc1 as doc UNION WITH doc1, studyIdsInScope, directAdmit WITH doc1, studyIdsInScope WHERE NOT directAdmit AND (NOT $nonSiteOnlyPatients0 OR doc1.patientId = ?) AND doc1.siteId <> ? MATCH (s:Site{tildaid:doc1.siteId, delpending:false})-[:PLAN_DEFINITION_TO_SITE]- (pd:DslPlanDefinition{delpending:false,type:?})-[:PLAN_DEFINITION_TO_STUDY]->(sst:Study) WHERE sst.tildaid IN studyIdsInScope RETURN doc1 as doc UNION WITH doc1, directAdmit WITH doc1 WHERE directAdmit RETURN doc1 as doc} WITH DISTINCT doc WITH doc, coalesce(doc.archived, false) as isArchived RETURN count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as readyForTmf, count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as needsCorrection, count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as needsAttention, count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as draft, count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as aiReview, count(CASE WHEN NOT isArchived AND doc.stateTag = ? THEN ? END) as manual, count(CASE WHEN isArchived THEN ? END) as archived
docsvc
1 1.10 s 1.95 s 1.10 s flat
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather th…
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather than at each use: // the sites, countries and roles below all hang off it. OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$ctxOrgId0})-[:FOR_USER]->(:User{tildaid:$ctxUserId0}) WHERE studyActor.state <> ? // Collect sites where user has membership in this study OPTIONAL MATCH (studyActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WITH org, study, studyActor, collect(DISTINCT site.tildaid) as userSiteIds // Collect countries where user has membership in this study OPTIONAL MATCH (studyActor)<-[:STUDYCOUNTRYACTOR_TO_ACTOR]-(sca:StudyCountryActor) WITH org, study, studyActor, userSiteIds, collect(DISTINCT sca.country) as userCountryIds // Pre-collect policies for this user/role in this study OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study) WHERE EXISTS { MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) } OR (studyActor IS NOT NULL AND EXISTS { MATCH (policy)-[:POLICY_FOR_ROLE]->(role:Role)<-[:APPOINTED_AS]-(studyActor) }) WITH study, studyActor, userSiteIds, userCountryIds, collect(policy) as policies WITH {study:study, studyActor:studyActor, userSiteIds:userSiteIds, userCountryIds:userCountryIds, policies:policies} AS st WITH collect(distinct st) as studies // studyById lets each document row look its context up by study id instead of scanning // the whole list. Entries whose study did not resolve share the ? key; they all carry // the same empty context, so which one wins does not matter. The node-bearing maps go no // further than this subquery; studyIdsInScope needs no lookup, since an id with no Study // node can never equal a matched study??ReadWrite?ReadOnly?s folder and ancestors (up to ? levels for TMF hierarchy) OPTIONAL MATCH (doc1)<-[:INCLUDES]-(docFolder:Folder) OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder) AS folderHierarchy // Check document ownership OPTIONAL MATCH (doc1)-[:UPLOADED_BY]->(uploader:User{tildaid:$policyUploaderId0}) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, folderHierarchy, uploader IS NOT NULL AS isOwnedByUser // Filter policies by: // ?. Folder: policy applies if folder is in hierarchy OR policy has no folder (study-wide). A folder-less // ownership policy is the role?Owned Documents?Non Owned Documents?Staging?Pre-Staging?Owned Documents?Non Owned Documents?My Sites Documents?Other Sites Documents?My Countries Documents?Other Countries Documents?Study Scope Documents?NoAccess?NoAccess?ReadOnly?ReadOnly?ReadWrite?ReadOnly?NoAccess??Inactive? ?Received?Quality Control?PHI Issues???/???/???/') ELSE ? END as childFolderPath WITH docObj, ct, child, st, head(collect(childFolderPath)) AS childFolderPath WITH child, CASE WHEN ct IS NULL THEN null ELSE { documentId: child.tildaid, studyId: COALESCE(child.studyId, ?), studyName: COALESCE(st.name, ?), folderPath: childFolderPath, kind: ct.kind, stale: ct.sourceVersionId <> docObj.primaryVersionId, version: COALESCE(child.versionName, toString(child.majorVersion)) } END as copiedToEntry RETURN collect(copiedToEntry) as copiedTo, count(DISTINCT child) as copiedToCount } CALL { return null as promotedTo } CALL { return null AS docReviewStatus, null AS hasImportantComment, null AS discrepancyCount } CALL { return null as tmfCategorization } with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total return document, total
docsvc
1 1.90 s 1.95 s 1.90 s flat
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather th…
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather than at each use: // the sites, countries and roles below all hang off it. OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$ctxOrgId0})-[:FOR_USER]->(:User{tildaid:$ctxUserId0}) WHERE studyActor.state <> ? // Collect sites where user has membership in this study OPTIONAL MATCH (studyActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WITH org, study, studyActor, collect(DISTINCT site.tildaid) as userSiteIds // Collect countries where user has membership in this study OPTIONAL MATCH (studyActor)<-[:STUDYCOUNTRYACTOR_TO_ACTOR]-(sca:StudyCountryActor) WITH org, study, studyActor, userSiteIds, collect(DISTINCT sca.country) as userCountryIds // Pre-collect policies for this user/role in this study OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study) WHERE EXISTS { MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) } OR (studyActor IS NOT NULL AND EXISTS { MATCH (policy)-[:POLICY_FOR_ROLE]->(role:Role)<-[:APPOINTED_AS]-(studyActor) }) WITH study, studyActor, userSiteIds, userCountryIds, collect(policy) as policies WITH {study:study, studyActor:studyActor, userSiteIds:userSiteIds, userCountryIds:userCountryIds, policies:policies} AS st WITH collect(distinct st) as studies // studyById lets each document row look its context up by study id instead of scanning // the whole list. Entries whose study did not resolve share the ? key; they all carry // the same empty context, so which one wins does not matter. The node-bearing maps go no // further than this subquery; studyIdsInScope needs no lookup, since an id with no Study // node can never equal a matched study?Basic??ReadWrite?ReadOnly?ReadWrite?ReadWrite?ReadOnly?NoAccess??Inactive? ?Received?Quality Control?PHI Issues???/???/???/') ELSE ? END as childFolderPath WITH docObj, ct, child, st, head(collect(childFolderPath)) AS childFolderPath WITH child, CASE WHEN ct IS NULL THEN null ELSE { documentId: child.tildaid, studyId: COALESCE(child.studyId, ?), studyName: COALESCE(st.name, ?), folderPath: childFolderPath, kind: ct.kind, stale: ct.sourceVersionId <> docObj.primaryVersionId, version: COALESCE(child.versionName, toString(child.majorVersion)) } END as copiedToEntry RETURN collect(copiedToEntry) as copiedTo, count(DISTINCT child) as copiedToCount } CALL { return null as promotedTo } CALL { return null AS docReviewStatus, null AS hasImportantComment, null AS discrepancyCount } CALL { return null as tmfCategorization } with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total return document, total
docsvc
2 925 ms 1.90 s 1.81 s flat
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(a…
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) WITH ph, e, es, del, acceptedDoc, coalesce(acceptedDoc, ph) AS d OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) OPTIONAL MATCH (siteFromEvent: Site{tildaid: es.scopeId}) WHERE es.scopeType = ? OPTIONAL MATCH (d)-[:DOC_TO_ORG]->(o:Organization) OPTIONAL MATCH (d)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) where da.key in [?, ?, ?, ?] OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (uploader: User) OPTIONAL MATCH (study) - [:STUDY_TO_ACTOR] - (actor: Actor{userId: uploader.tildaid}) WITH d, e, es, del, b, study, b2, coalesce(site, siteFromEvent) AS site, o, head(collect(da)) as da, currentFolder, currentSubArtifact, stagingInfo, artifact, section, zone, collection, subArtifact, uploader, ph, acceptedDoc, head(collect(actor)) as actor CALL apoc.create.vNode([?], { tildaid: ph.tildaid, createdat: ph.createdat, updatedat: ph.updatedat, documentId: acceptedDoc.tildaid, documentCreatedAt: acceptedDoc.createdat, documentUpdatedAt: acceptedDoc.updatedat, orgName: o.name, orgId: o.tildaid, orgAlias: o.alias, studyName: study.name, studyId: study.tildaid, siteName: site.name, siteId: site.tildaid, documentStatus: d.status, country: CASE WHEN es.scopeType = ? THEN coalesce(es.scopeId, d.country) ELSE d.country END, languages: coalesce(apoc.convert.toJson(d.languages), ?), fileName: d.fileName, uploadedByName: uploader.name, uploadedById: uploader.tildaid, collection: d.collection, stateTag: d.stateTag, documentExpiryDate: da.value, zone: zone.name, zoneId: zone.tildaid, zoneNumber: case when zone.number is null then null else apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) end, section: section.name, sectionId: section.tildaid, sectionNumber: case when section.number is null then null else apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) end, artifact: artifact.name, artifactId: artifact.tildaid, artifactNumber: case when artifact.number is null then null else apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) end, subArtifact: subArtifact.name, subArtifactId: subArtifact.tildaid, subArtifactNumber: case when subArtifact.number is null then null else apoc.text.lpad(toString(COALESCE(subArtifact.number, ?)), ?, ?) end, eventId: e.tildaid, eventCreatedAt: e.createdat, eventType: e.type, eventName: e.name, eventStatus: e.status, eventScopeId: es.tildaid, eventScopeName: es.name, scopeType: es.scopeType, eventScopeDecision: es.decision, deliverableId: del.tildaid, deliverableName: del.name, deliverableLocationId: del.locationId, deliverableLocationType: del.locationType, deliverableType: del.type, roles: actor.roles }) YIELD node return node
write dashrsvc
2 960 ms 1.90 s 1.89 s flat
WITH ? as total, null as vector MATCH (doc:Document) WHERE doc.tildaid in $lists.mf0 MATCH (org:Organization{tildaid:$policyOrgId0}) CALL { WITH org, doc UNWIND [doc.studyId] AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = st…
WITH ? as total, null as vector MATCH (doc:Document) WHERE doc.tildaid in $lists.mf0 MATCH (org:Organization{tildaid:$policyOrgId0}) CALL { WITH org, doc UNWIND [doc.studyId] AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather than at each use: // the sites, countries and roles below all hang off it. OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$ctxOrgId0})-[:FOR_USER]->(:User{tildaid:$ctxUserId0}) WHERE studyActor.state <> ? // Collect sites where user has membership in this study OPTIONAL MATCH (studyActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WITH org, study, studyActor, collect(DISTINCT site.tildaid) as userSiteIds // Collect countries where user has membership in this study OPTIONAL MATCH (studyActor)<-[:STUDYCOUNTRYACTOR_TO_ACTOR]-(sca:StudyCountryActor) WITH org, study, studyActor, userSiteIds, collect(DISTINCT sca.country) as userCountryIds // Pre-collect policies for this user/role in this study OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study) WHERE EXISTS { MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) } OR (studyActor IS NOT NULL AND EXISTS { MATCH (policy)-[:POLICY_FOR_ROLE]->(role:Role)<-[:APPOINTED_AS]-(studyActor) }) WITH study, studyActor, userSiteIds, userCountryIds, collect(policy) as policies WITH {study:study, studyActor:studyActor, userSiteIds:userSiteIds, userCountryIds:userCountryIds, policies:policies} AS st WITH collect(distinct st) as studies // studyById lets each document row look its context up by study id instead of scanning // the whole list. Entries whose study did not resolve share the ? key; they all carry // the same empty context, so which one wins does not matter. The node-bearing maps go no // further than this subquery; studyIdsInScope needs no lookup, since an id with no Study // node can never equal a matched study?tildaScale?ReadWrite?ReadOnly?s folder and ancestors (up to ? levels for TMF hierarchy) OPTIONAL MATCH (doc1)<-[:INCLUDES]-(docFolder:Folder) OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder) AS folderHierarchy // Check document ownership OPTIONAL MATCH (doc1)-[:UPLOADED_BY]->(uploader:User{tildaid:$policyUploaderId0}) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, folderHierarchy, uploader IS NOT NULL AS isOwnedByUser // Filter policies by: // ?. Folder: policy applies if folder is in hierarchy OR policy has no folder (study-wide). A folder-less // ownership policy is the role?Owned Documents?Non Owned Documents?Staging?Pre-Staging?Owned Documents?Non Owned Documents?My Sites Documents?Other Sites Documents?My Countries Documents?Other Countries Documents?Study Scope Documents?NoAccess?NoAccess?ReadOnly?ReadOnly?ReadWrite?ReadOnly?NoAccess? ???/') as folderPath, folderConstraints RETURN {id: fold.tildaid, name: fold.name, number: fold.number, folderType: fold.folderType, folderScope: fold.scope, folderScopeId: fold.scopeId, path: folderPath, mandatoryFields: [], optionalFields: [], subArtifactId: COALESCE(subArtifact.tildaid, ?), subArtifactName: COALESCE(subArtifact.name, ?), constraints: CASE WHEN folderConstraints IS NOT NULL THEN folderConstraints{.*} ELSE null END} AS primaryFolder } CALL { return null as listingFolders } CALL { return null as stateTags2, null as stagingLabels } CALL { return [] as reviews} CALL { WITH doc // A regular source an instance was made from (ENG-?) is not shown as its template. optional match (doc)-[templateUse:USES_TEMPLATE]->(template:Document {template: true})-[:DOC_TO_ORG]-(torg:Organization) return case when not template is null then {doc: template{.*}, org: torg{.*}, orgId: torg.tildaid, tildaid: template.tildaid, currentGeneration: toString(template.generation), generationInUse: toString(templateUse.templateGenerationUsed)} else null end as templateDetails } CALL { with doc, docObj CALL { with doc, docObj with doc, docObj where doc.primaryVersionId = ? or doc.primaryVersionId is null optional match (docObj)-[:ALL_CHANGELOG]->(c:ChangeLog) optional match(e: ESignature{tildaid:c.blob}) optional match (doc)-[:UPLOADED_BY]->(uploader:User) with doc, uploader, case when not c is null then {changeLog: c{.*}, eSignature: e{.*}} end as clog order by c.createdat asc with doc, uploader, collect(clog) as changeLog return {tildaid: doc.tildaid, fileName: doc.fileName, originalFileName: doc.originalFileName, contentType: doc.contentType, date: doc.date, dateType: doc.dateType, versionName: doc.versionName, rejectReason: doc.rejectReason, language: doc.language, languages: doc.languages, needsAttention: doc.needsAttention, needsAttentionReasons: doc.needsAttentionReasons, trackActiveDuplicates: doc.trackActiveDuplicates, versionNumber: doc.versionNumber, majorVersion: doc.majorVersion, minorVersion: doc.minorVersion, generation: toString(doc.generation), cksum: doc.cksum, size: doc.size, status: doc.status, stateTag: doc.stateTag, pages: doc.pages, uploadMethod: doc.uploadMethod, changeLog: changeLog, createdAt: doc.createdat, renderedCompletedAt: doc.renderedCompletedAt, renderedFieldsRevision: doc.renderedFieldsRevision, renderedSignersRevision: doc.renderedSignersRevision, uploadedBy: case when uploader is null then null else {id: uploader.tildaid, name: uploader.name, profilePicURL: uploader.profilePicURL} end} as version union with doc optional match (doc)-[:DOCUMENT_VERSION]->(dv:DocumentVersion) where $versionsSeesPreRedaction0 or coalesce(dv.preRedaction, false) = false or dv.tildaid = doc.primaryVersionId optional match (doc)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:DOCUMENT_SOURCE_TO_VERSION]->(dv) optional match (dv)-[:UPLOADED_BY]->(uploader:User) optional match (dv)-[:ALL_CHANGELOG]->(c:ChangeLog) optional match(e: ESignature{tildaid:c.blob}) optional match (dv)-[:PROMOTED_FROM]->(psrc:Document) with doc, dv, ds, psrc, uploader, case when not c is null then {changeLog: c{.*}, eSignature: e{.*}} end as clog order by c.createdat asc with doc, dv, ds, psrc, uploader, collect(clog) as changeLog return case when dv is null then null else {tildaid: dv.tildaid, fileName: dv.fileName, originalFileName: dv.originalFileName, contentType: dv.contentType, date: dv.date, dateType: doc.dateType, versionName: dv.versionName, rejectReason: dv.rejectReason, language: dv.language, languages: dv.languages, needsAttention: dv.needsAttention, needsAttentionReasons: dv.needsAttentionReasons, trackActiveDuplicates: dv.trackActiveDuplicates, versionNumber: dv.versionNumber, majorVersion: dv.majorVersion, minorVersion: dv.minorVersion, documentSourceId: ds.tildaid, promotedFromDocumentId: psrc.tildaid, generation: toString(dv.generation), linearizedPdfUrl: dv.linearizedPdfUrl, linearizedPdfStatus: dv.linearizedPdfStatus, needsProcessedPdf: dv.needsProcessedPdf, langDetectionStatus: dv.langDetectionStatus, translatedPdfUrl: dv.translatedPdfUrl, translatedPdfStatus: dv.translatedPdfStatus, cksum: dv.cksum, size: dv.size, status: dv.status, stateTag: dv.stateTag, pages: dv.pages, uploadMethod: dv.uploadMethod, changeLog: changeLog, url: dv.url, createdAt: dv.createdat, renderedCompletedAt: dv.renderedCompletedAt, renderedFieldsRevision: dv.renderedFieldsRevision, renderedSignersRevision: dv.renderedSignersRevision, preRedaction: coalesce(dv.preRedaction, false), uploadedBy: case when uploader is null then null else {id: uploader.tildaid, name: uploader.name, profilePicURL: uploader.profilePicURL} end} end as version // TODO: [Major/Minor Versioning] Once client supports minor version UX, update ordering to: // } with doc, version order by version.majorVersion, version.minorVersion // This will display versions as ?.?, ?.?, ?.? instead of flat ?, ?, ? } with doc, version order by version.versionNumber where $versionsVersionId0 IS NULL OR version.tildaid = $versionsVersionId0 return collect(version) as versions } CALL { with doc optional match (doc)-[:HAS]-(ann:DocumentAnnotation) optional match (ann)-[:ANNOTATION_SIGNER]-(ds:DocumentSigner) optional match (ann)-[:ANNOTATION_FIELD]-(df:DocumentField) optional match (ann)-[:ALL_CHANGELOG]->(cl:ChangeLog) with doc, ann, ds, df, cl order by cl.createdat asc with doc, ann, apoc.convert.fromJsonList(ann.signers) as signersOld, collect(distinct ds{.*}) as signers, collect(distinct df{.*}) as fields, collect(distinct cl{.*}) as auditLog with doc, ann, auditLog, case when size(signers) <> ? then signers when size(signers) = ? and signersOld is null then [] else signersOld end as signers1, case when fields is null then [] else fields end as fields1 with doc, ann{.*, signers: signers1, fields: fields1} as annotationSpec, case when doc.template = false then ann{.*, signers: signers1, fields: fields1} else null end as annotationVal return annotationSpec, annotationVal } CALL { return [] as queries } CALL { return null as sharedWith, null as currentShare } CALL { WITH doc MATCH (doc)-[:DOCUMENT_VERSION]->(dv:DocumentVersion{tildaid: coalesce($docAttrVersionId0, doc.primaryVersionId)}) MATCH (doc)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:DOCUMENT_SOURCE_TO_VERSION]->(dv) OPTIONAL MATCH (ds)-[:HAS_ATTRIBUTE]->(da:DocAttribute) OPTIONAL MATCH (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) RETURN collect(DISTINCT da{.*, name: COALESCE(dad.name, ?), description: COALESCE(dad.description, ?), documentSourceId: ds.tildaid}) as docAttributes } CALL { return null as notesCount } CALL { return null as eventId, null as eventName, null as deliverable, null as eventScope } CALL { return null as copiedFrom } CALL { return [] as copiedTo, ? as copiedToCount } CALL { WITH doc OPTIONAL MATCH (doc)<-[:PROMOTED_FROM]-(tv:DocumentVersion)<-[:DOCUMENT_VERSION]-(tgt:Document) WITH CASE WHEN tgt IS NULL THEN null ELSE { documentId: tgt.tildaid, targetVersionId: tv.tildaid, version: COALESCE(tv.versionName, toString(tv.majorVersion)) } END as promotedTo RETURN promotedTo LIMIT ? } CALL { return null AS docReviewStatus, null AS hasImportantComment, null AS discrepancyCount } CALL { WITH docObj OPTIONAL MATCH (docObj)<-[:INCLUDES]-(currentFolder:Folder{folderType:?}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (docObj)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (docObj)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) WITH CASE WHEN stagingInfo IS NOT NULL THEN {artifactId: stagingInfo.folderId, subArtifactId: stagingInfo.subArtifactId, stagingInfoId: stagingInfo.tildaid} WHEN currentFolder.level = ? THEN {artifactId: currentFolder.tildaid, subArtifactId: COALESCE(currentSubArtifact.tildaid, ?), stagingInfoId: ?} ELSE {artifactId: ?, subArtifactId: ?, stagingInfoId: ?} END as tmfInfo WITH tmfInfo.artifactId as artifactId, tmfInfo.subArtifactId as subArtifactId, tmfInfo.stagingInfoId as stagingInfoId OPTIONAL MATCH (artifact:Folder{tildaid:artifactId})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{name:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(allSubArtifacts:SubArtifact) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:subArtifactId}) OPTIONAL MATCH (subArtifact)-[:HAS]->(subArtifactConstraints:FolderConstraints) OPTIONAL MATCH (artifact)-[:HAS]->(artifactConstraints:FolderConstraints) WITH artifactId as folderId, subArtifactId, stagingInfoId, CASE WHEN zone.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+ ? + zone.name, ?) ELSE ? END as zone, CASE WHEN section.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+?+apoc.text.lpad(toString(section.number), ?, ?)+ ? + section.name, ?) ELSE ? END as section, CASE WHEN artifact.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+?+apoc.text.lpad(toString(section.number), ?, ?)+?+apoc.text.lpad(toString(artifact.number), ?, ?)+ ? + artifact.name, ?) ELSE ? END as artifact, CASE WHEN subArtifact.number IS NOT NULL THEN COALESCE(apoc.text.lpad(toString(zone.number), ?, ?)+?+apoc.text.lpad(toString(section.number), ?, ?)+?+apoc.text.lpad(toString(artifact.number), ?, ?)+?+apoc.text.lpad(toString(subArtifact.number), ?, ?)+ ? + subArtifact.name, ?) ELSE ? END as subArtifact, COALESCE(subArtifact.name, ?) as subArtifactName, COALESCE(subArtifactConstraints, artifactConstraints) as constraints, NOT(COUNT(allSubArtifacts) > ? AND subArtifactId = ?) as subArtifactValid OPTIONAL MATCH (constraints)-[:HAS_ATTRIBUTE_CONSTRAINT]->(dac:DocAttributeConstraint) WITH folderId, subArtifactId, stagingInfoId, zone, section, artifact, subArtifact, subArtifactName, subArtifactValid, constraints, COLLECT(DISTINCT CASE WHEN dac IS NOT NULL THEN {id: dac.tildaid, key: dac.key, order: dac.order, isMandatory: dac.isMandatory, description: dac.description} ELSE NULL END) as attributeConstraintsList WITH folderId, subArtifactId, stagingInfoId, zone, section, artifact, subArtifact, subArtifactName, subArtifactValid, CASE WHEN constraints IS NOT NULL THEN { id: constraints.tildaid, mandatory: constraints.mandatory, tmfLevel: constraints.tmfLevel, attributeConstraints: [attr IN attributeConstraintsList WHERE attr IS NOT NULL | attr] } ELSE null END as constraints WITH {folderId: folderId, subArtifactId: subArtifactId, stagingInfoId: stagingInfoId, zone: zone, section: section, artifact: artifact, subArtifact: subArtifact, subArtifactName: subArtifactName, subArtifactValid: subArtifactValid, path: apoc.text.join([x IN [zone, section, artifact] WHERE x <> ? | x], ?), constraints: constraints} as tmfCategorization RETURN CASE WHEN tmfCategorization.folderId=? THEN null ELSE tmfCategorization END as tmfCategorization } with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total return document, total
docsvc
2 990 ms 1.90 s 1.97 s flat
MATCH (dv:DocumentVersion {tildaid: $id})<-[:DOCUMENT_VERSION]-(d:Document) MATCH (r:Recommendation) WHERE r.doc_id = d.tildaid AND NOT r.category IN [?, ?, ?] MATCH (ai:AIRun)-[:AI_RUN_TO_RECOMMENDATION]->(r) MATCH (r)-[:RECOMMENDATION_TO…
MATCH (dv:DocumentVersion {tildaid: $id})<-[:DOCUMENT_VERSION]-(d:Document) MATCH (r:Recommendation) WHERE r.doc_id = d.tildaid AND NOT r.category IN [?, ?, ?] MATCH (ai:AIRun)-[:AI_RUN_TO_RECOMMENDATION]->(r) MATCH (r)-[:RECOMMENDATION_TO_ORGANIZATION]->(o:Organization) OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (u: User) OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) CALL { WITH d, site OPTIONAL MATCH (study:Study{tildaid: d.studyId})<-[:PLAN_DEFINITION_TO_STUDY]- (pd:DslPlanDefinition{delpending:false, type:?})-[:PLAN_DEFINITION_TO_SITE]->(site) RETURN COALESCE(pd.siteNumber, ?) AS siteNumber } CALL { WITH dv OPTIONAL MATCH (dv)<-[:DOCUMENT_SOURCE_TO_VERSION]-(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(docAttr: DocAttribute) WITH collect([coalesce(docAttr.key, ?), coalesce(docAttr.value, ?)]) AS attributePairs WITH apoc.map.fromPairs(attributePairs) AS attributesMap With apoc.convert.toJson(attributesMap) AS attributesJson RETURN attributesJson } WITH r, ai, o, d, dv, zone, section, artifact, subArtifact, u, study, site, siteNumber, attributesJson, // Document Scope key stripping: reduce the rec JSON envelope to the keys // valid for its OWN scope so irrelevant keys (e.g. country on a site rec) // don?scope?country?scope?siteName?siteId?siteNumber?scope?scope???{}?{}?????{}?{?{?{????DRecommendation?t emit irrelevant rows (e.g. country on a site-scoped rec). recommendedValue: CASE WHEN r.category = ? AND recAllowed IS NOT NULL THEN apoc.convert.toJson(apoc.map.fromPairs([k IN keys(recMap) WHERE k IN recAllowed | [k, recMap[k]]])) ELSE coalesce(CASE WHEN coalesce(r.data_formatted, ?) <> ? THEN r.data_formatted END, r.data, ?) END, // originalRecommendation = the AI?Document Scope????Document Metadata?{}??Document Scope???Document Naming??TMF Artifact???.???.??? ??Sub Artifact??Document Scope??country???site?????Document Metadata??Document Metadata?????{}??????{}??{}??Document Scope?????country???site??????Document Naming???????TMF Artifact????????.???.??? ??Sub Artifact???????Document Format?Document Summary?Quality Control?PHI Issues?Confirm?Fix It????' } ) YIELD node RETURN node
write dashrsvc
3 570 ms 1.85 s 1.70 s flat
match (task:Task)-[:BELONGS_TO]->(inbox:Inbox)-[:MANAGED_BY]->(Organization) where (Organization.alias = $scopeOrgAlias) match (task)-[:BELONGS_TO]->(SInbox:Inbox{type:?})-[:MANAGED_BY]->(Study:Study{name:$scopeStudyName})-[:STUDY_TO_ORG]-…
match (task:Task)-[:BELONGS_TO]->(inbox:Inbox)-[:MANAGED_BY]->(Organization) where (Organization.alias = $scopeOrgAlias) match (task)-[:BELONGS_TO]->(SInbox:Inbox{type:?})-[:MANAGED_BY]->(Study:Study{name:$scopeStudyName})-[:STUDY_TO_ORG]->(Organization) where (Organization.alias = $scopeOrgAlias) with task match (task)-[:WITH_CONTEXT]->() where task.state <> ? optional match (task)-[:BELONGS_TO]-(SInbox:Inbox{type:?})-[:MANAGED_BY]-(UserStudy:Study)-[:STUDY_TO_ORG]-(Organization) where (Organization.alias = $scopeOrgAlias) with task, SInbox, UserStudy where (SInbox IS NULL) OR (SInbox IS NOT NULL AND exists((:User{email:$scopeUserEmail})-[:FOR_USER]-(:Actor)-[:STUDY_TO_ACTOR]-(UserStudy))) WITH collect(DISTINCT task) AS tasks WITH tasks, size(tasks) AS taskCount CALL { WITH tasks UNWIND tasks AS task RETURN collect(DISTINCT task.state) AS states, collect(DISTINCT task.category) AS categories, collect(DISTINCT coalesce(task.priority, ?)) AS priorities } CALL { WITH tasks UNWIND tasks AS task OPTIONAL MATCH (task)-[:CREATED_BY]-(cu:User) RETURN collect(DISTINCT {id: cu.tildaid, name: cu.name}) AS createdby } CALL { WITH tasks UNWIND tasks AS task OPTIONAL MATCH (task)-[:BELONGS_TO]->(:Inbox)-[:OWNED_BY]->(au:User) RETURN collect(DISTINCT {id: au.tildaid, name: au.name}) AS assignees } CALL { WITH tasks UNWIND tasks AS task OPTIONAL MATCH (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(ist:Study) OPTIONAL MATCH (task)-[:WITH_CONTEXT]->(cst:Study) RETURN collect(DISTINCT ist.name) + collect(DISTINCT cst.name) AS studiesShallow } CALL { WITH tasks UNWIND tasks AS task OPTIONAL MATCH (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(isite:Site) RETURN collect(DISTINCT isite.name) AS sitesShallow } CALL { WITH tasks UNWIND tasks AS task OPTIONAL MATCH (task)-[:WITH_CONTEXT|CREATED_FROM_SPEC]->(vctx) OPTIONAL MATCH (vctx)-[:BELONGS_TO_VISIT]->(v1) WHERE v1:Visit OR v1:VisitV2 OPTIONAL MATCH (vctx)<-[:OBJ_TO_ADVERSE_EVENT|PROTOCOL_DEVIATION|PARTICIPANT_ACTIVITY|PARTICIPANT_VISIT_TO_ALL_PARTICIPANT_PREVISIT|PARTICIPANT_VISIT_TO_ALL_PARTICIPANT_POSTVISIT]-(pv:ParticipantVisit)-[:BELONGS_TO_VISIT]->(v2) WHERE v2:Visit OR v2:VisitV2 OPTIONAL MATCH (vctx)-[:BUDGET_REPORT_TO_PARTICIPANT_VISIT]->(:ParticipantVisit)-[:BELONGS_TO_VISIT]->(v3) WHERE v3:Visit OR v3:VisitV2 OPTIONAL MATCH (:ParticipantQuestion{tildaid: vctx.scopeId})<-[:PARTICIPANT_QUESTION]-(:ParticipantQuestionnaire)<-[:PARTICIPANT_QUESTIONNAIRE]-(:ParticipantActivity)<-[:PARTICIPANT_ACTIVITY]-(:ParticipantVisit)-[:BELONGS_TO_VISIT]->(v4) WHERE v4:Visit OR v4:VisitV2 OPTIONAL MATCH (vctx)<-[:PARTICIPANT_DSLTASK_TO_ALL_PARTICIPANT_DSLSPECNODE]-(:ParticipantDslTask)<-[:PARTICIPANT_PRE_POST_VISIT_TO_ALL_PARTICIPANT_TASKS]-(:ParticipantPrePostVisit)<-[:PARTICIPANT_VISIT_TO_ALL_PARTICIPANT_PREVISIT|PARTICIPANT_VISIT_TO_ALL_PARTICIPANT_POSTVISIT]-(:ParticipantVisit)-[:BELONGS_TO_VISIT]->(v5) WHERE v5:Visit OR v5:VisitV2 RETURN collect(DISTINCT v1.title) + collect(DISTINCT v2.title) + collect(DISTINCT v3.title) + collect(DISTINCT v4.title) + collect(DISTINCT v5.title) AS visits } CALL { WITH tasks UNWIND tasks AS task OPTIONAL MATCH (task)-[:WITH_CONTEXT|CREATED_FROM_SPEC]->(ctx) OPTIONAL MATCH (ctx)-[:LEAD_TO_STUDY]->(lst:Study) OPTIONAL MATCH (ctx)-[:LEAD_TO_PATIENT]->(lp:Patient) OPTIONAL MATCH (ctx)-[:BUDGET_REPORT_TO_PARTICIPANT_VISIT]->(brpv:ParticipantVisit) OPTIONAL MATCH (ctx)-[:PARTICIPANT_TABLE]->(ptab:ParticipantTable) OPTIONAL MATCH (ctx)-[:PLAN_DEFINITION_TO_STUDY]->(dst:Study) WHERE ctx.delpending = false OPTIONAL MATCH (ctx)-[:PLAN_DEFINITION_TO_SITE]->(dsite:Site) WHERE ctx.delpending = false OPTIONAL MATCH (ctx)<-[:STUDY_TO_ACTOR]-(ast:Study) OPTIONAL MATCH (ctx)<-[:STAFF_TO_ACTOR]-(asite:Site{status:?}) OPTIONAL MATCH (ctx)-[:WORKPLAN_TO_STUDY]->(wst:Study) OPTIONAL MATCH (ctx)-[:WORKPLAN_TO_SITE]->(wsite:Site) WITH ctx, lst, lp, dst, dsite, ast, asite, wst, wsite, coalesce(ctx.carePlanId, ctx.careplanId, CASE WHEN ctx:CarePlan THEN ctx.tildaid END, brpv.carePlanId, ptab.carePlanId) AS cpid OPTIONAL MATCH (cp:CarePlan{tildaid: cpid})-[:EXECUTE_SOA]->(:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition{delpending:false})-[:PLAN_DEFINITION_TO_STUDY]->(pst:Study) OPTIONAL MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]->(psite:Site) OPTIONAL MATCH (p:Patient{tildaid: cp.participantId}) WITH lst, dst, ast, wst, pst, dsite, asite, wsite, psite, coalesce(p, lp, CASE WHEN ctx:Patient THEN ctx END) AS pat OPTIONAL MATCH (phi:Phi{tildaid: pat.phiid}) RETURN collect(DISTINCT pst.name) + collect(DISTINCT lst.name) + collect(DISTINCT dst.name) + collect(DISTINCT ast.name) + collect(DISTINCT wst.name) AS studiesDeep, collect(DISTINCT psite.name) + collect(DISTINCT dsite.name) + collect(DISTINCT asite.name) + collect(DISTINCT wsite.name) AS sitesDeep, collect(DISTINCT {id: phi.tildaid, fullname: phi.fullname, firstname: phi.firstname, middlename: phi.middlename, lastname: phi.lastname, suffix: phi.suffix}) AS subjects } RETURN { taskCount: taskCount, states: states, categories: categories, priorities: priorities, createdby: createdby, assignees: assignees, studies: studiesShallow + studiesDeep, sites: sitesShallow + sitesDeep, visits: visits, subjects: subjects } AS facets
tusker
4 382 ms 1.80 s 1.46 s flat
MATCH (dv:DocumentVersion {tildaid: $id})<-[:DOCUMENT_VERSION]-(d:Document) OPTIONAL MATCH (d)-[:DOC_TO_ORG]->(o:Organization) OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Bind…
MATCH (dv:DocumentVersion {tildaid: $id})<-[:DOCUMENT_VERSION]-(d:Document) OPTIONAL MATCH (d)-[:DOC_TO_ORG]->(o:Organization) OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) OPTIONAL MATCH (fm:FolderManager{scopeId:d.studyId})-[:HAS]->(dad:DocAttributeDefinition) where dad.name = ? or dad.name = ? OPTIONAL MATCH (dv)<-[:DOCUMENT_SOURCE_TO_VERSION]-(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(da: DocAttribute{key: dad.code}) OPTIONAL MATCH (dv) - [:UPLOADED_BY] - (u: User) OPTIONAL MATCH (d)<-[:INCOMING_EMAIL_TO_DOCUMENT]-(ie:IncomingEmail) OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) CALL { WITH dv OPTIONAL MATCH (dv)-[:ALL_CHANGELOG]-(dvcl:ChangeLog) WHERE dvcl.type IN [?, ?, ?] AND (dvcl.newValue CONTAINS ? OR dvcl.newValue CONTAINS ?) WITH dvcl ORDER BY dvcl.createdat DESC LIMIT ? OPTIONAL MATCH (changer:User {tildaid: dvcl.changedBy}) WHERE dvcl IS NOT NULL RETURN coalesce(apoc.convert.fromJsonMap(coalesce(dvcl.newValue, ?)).stateTag, apoc.convert.fromJsonMap(coalesce(dvcl.newValue, ?)).stagingStatus, ?) as newStatus, coalesce(apoc.convert.fromJsonMap(coalesce(dvcl.oldValue, ?)).stateTag, apoc.convert.fromJsonMap(coalesce(dvcl.oldValue, ?)).stagingStatus, ?) as previousStatus, coalesce(dvcl.changedBy, ?) as changedBy, coalesce(changer.name, ?) as changedByName } CALL { WITH dv OPTIONAL MATCH (dv)<-[:DOCUMENT_SOURCE_TO_VERSION]-(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(docAttr: DocAttribute) WITH collect([coalesce(docAttr.key, ?), coalesce(docAttr.value, ?)]) AS attributePairs WITH apoc.map.fromPairs(attributePairs) AS attributesMap WITH apoc.convert.toJson(attributesMap) AS attributesJson RETURN attributesJson } WITH d, dv, o, study, site, u, ie, zone, section, artifact, subArtifact, da, newStatus, previousStatus, changedBy, changedByName, attributesJson CALL apoc.create.vNode([?], { tildaid: dv.tildaid, documentId: d.tildaid, displayId: d.displayId, status: d.status, docTypeName: d.docTypeName, tmfLevel: d.tmfLevel, pages: d.pages, versionNumber: dv.versionNumber, versionId: dv.tildaid, userId: d.userId, uploadedByName: u.name, uploadedById: u.tildaid, uploadedAt: toString(d.createdat), archived: d.archived, aiEnabled: d.aiEnabled, language: d.language, uploadMethod: coalesce(NULLIF(d.uploadMethod, ?), dv.uploadMethod), country: d.country, orgId: o.tildaid, orgName: o.name, orgAlias: o.alias, studyId: study.tildaid, studyName: study.name, siteId: site.tildaid, siteName: site.name, createdat: d.createdat, updatedat: d.updatedat, zone: zone.name, zoneId: zone.tildaid, section: section.name, sectionId: section.tildaid, artifact: artifact.name, artifactId: artifact.tildaid, subArtifact: subArtifact.name, subArtifactId: subArtifact.tildaid, artifactNumber: apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?), subArtifactNumber: case when subArtifact.number is null then null else apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(subArtifact.number, ?)), ?, ?) end, docDate: da.value, submissionDelayDays: CASE WHEN da.value IS NULL THEN NULL ELSE toString( toInteger(ceil((d.createdat - datetime(da.value).epochMillis)/?)) ) END, collection: d.collection, stateTag: dv.stateTag, needsAttentionReasons: dv.needsAttentionReasons, previousStatus: previousStatus, newStatus: newStatus, changedBy: changedBy, changedByName: changedByName, patientId: d.patientId, fileName: d.fileName, documentMetadata: attributesJson, emailSubject: ie.subject, emailMessageId: ie.message_id } ) YIELD node RETURN node
write dashrsvc
5 407 ms 1.75 s 2.00 s flat
MATCH (e:Event {tildaid: $eventId, orgId: $orgId})- [:REQUIRES_RESOLUTION_OF]->(d:Deliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document) MATCH (ph)-[:SCOPED_TO]->(es:EventScope)-[:PART_OF_EVENT]->(e) OPTIONAL MATCH (wo:WorkOrder)-[woCl…
MATCH (e:Event {tildaid: $eventId, orgId: $orgId})- [:REQUIRES_RESOLUTION_OF]->(d:Deliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document) MATCH (ph)-[:SCOPED_TO]->(es:EventScope)-[:PART_OF_EVENT]->(e) OPTIONAL MATCH (wo:WorkOrder)-[woClaim:WO_CLAIMS_PLACEHOLDER]->(ph) WHERE woClaim.is_current = true WITH e, ph, d, es, tracksRel, head(collect(wo)) AS wo OPTIONAL MATCH (ph)-[acceptedRel:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) OPTIONAL MATCH (ph)-[matchRel:MATCHING_DOCUMENT]->(matchDoc:Document) WHERE acceptedDoc IS NULL OR matchDoc.tildaid <> acceptedDoc.tildaid WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND matchDoc.collection = ? THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as matchesExpectedCollected, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND (matchDoc.collection IS NULL OR matchDoc.collection <> ?) THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as pendingMatchesCollected WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, [m IN matchesExpectedCollected WHERE m IS NOT NULL] as matchesExpected, [m IN pendingMatchesCollected WHERE m IS NOT NULL] as pendingMatches WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, CASE WHEN ph.status <> ? THEN ? WHEN acceptedDoc IS NOT NULL THEN ? WHEN SIZE(matchesExpected) > ? AND SIZE(pendingMatches) = ? THEN ? WHEN SIZE(matchesExpected) > ? OR SIZE(pendingMatches) > ? THEN ? ELSE ? END as matchStatus OPTIONAL MATCH (ph)-[renewalRel:RENEWAL_OF]->(renewalSrcCandidate:Document) WHERE coalesce(renewalRel.primary, false) = true AND coalesce(renewalSrcCandidate.archived, false) = false WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, head(collect(renewalSrcCandidate)) AS renewalSrc OPTIONAL MATCH (e)<-[:EVENTS]-(:EventPlan)-[:PLAN_CONTEXT]->(study) OPTIONAL MATCH (site:Site {tildaid: es.scopeId}) OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition {type: ?})-[:PLAN_DEFINITION_TO_STUDY]->(study) WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, renewalSrc, COALESCE(head(collect(site.name)), ?) AS siteName, COALESCE(head(collect(pd.siteNumber)), ?) AS siteNumber ORDER BY CASE es.scopeType WHEN ? THEN ? WHEN ? THEN ? ELSE ? END, es.name, d.name ASC, ph.fileName ASC WITH es, collect({ placeholderId: ph.tildaid, placeholderName: ph.fileName, deliverableId: d.tildaid, deliverableName: d.name, eventScopeId: es.tildaid, scopeType: es.scopeType, country: CASE WHEN es.scopeType = ? THEN COALESCE(es.scopeId, ?) ELSE ? END, siteNumber: siteNumber, siteName: siteName, dueDate: es.dueDate, matchStatus: matchStatus, userDecision: COALESCE(tracksRel.state, ?), archived: coalesce(ph.archived, false) OR es.status = ?, origin: COALESCE(tracksRel.origin, ?), feedback: COALESCE(tracksRel.feedback, ?), reason: COALESCE(tracksRel.reason, ?), removalHasAcceptedDocument: COALESCE(tracksRel.removalHasAcceptedDocument, false), workOrder: CASE WHEN wo IS NOT NULL THEN {tildaid: wo.tildaid, work_order_id: wo.work_order_id, status: wo.status} ELSE null END, renewalSource: CASE WHEN renewalSrc IS NOT NULL THEN {documentId: renewalSrc.tildaid, documentName: renewalSrc.fileName} ELSE null END, acceptedMatch: CASE WHEN acceptedDoc IS NOT NULL THEN { documentId: acceptedDoc.tildaid, displayId: acceptedDoc.displayId, contentType: acceptedDoc.contentType, documentName: acceptedDoc.fileName, documentDate: CASE WHEN acceptedDoc.createdat IS NOT NULL THEN datetime({epochMillis: acceptedDoc.createdat}) ELSE null END, isAccepted: true, matchedBy: COALESCE(head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | amr.matchedBy ]), ?), matchedAt: head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | CASE WHEN amr.createdat IS NOT NULL THEN datetime({epochMillis: amr.createdat}) ELSE null END ]), acceptedBy: acceptedRel.acceptedBy, acceptedAt: CASE WHEN acceptedRel.createdat IS NOT NULL THEN datetime({epochMillis: acceptedRel.createdat}) ELSE null END, docAttributes: [ (acceptedDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: acceptedDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } ELSE null END, matchesExpected: matchesExpected, pendingMatches: pendingMatches }) as scopeRows WITH es, scopeRows, { total: size(scopeRows), pending: size([pp IN scopeRows WHERE pp.userDecision IN [?, ?]]), pendingForDelete: size([pp IN scopeRows WHERE pp.userDecision = ?]), accepted: size([pp IN scopeRows WHERE pp.userDecision = ?]), rejected: size([pp IN scopeRows WHERE pp.userDecision = ?]), incomplete: size([pp IN scopeRows WHERE pp.matchStatus = ?]), potentialMatchFound: size([pp IN scopeRows WHERE pp.matchStatus = ?]), matchExpected: size([pp IN scopeRows WHERE pp.matchStatus = ?]), matched: size([pp IN scopeRows WHERE pp.matchStatus = ?]), finalized: size([pp IN scopeRows WHERE pp.matchStatus = ?]), completion: {required: size([pp IN scopeRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]), completed: size([pp IN scopeRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]), aiSuggested: size([pp IN scopeRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), inStaging: size([pp IN scopeRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), noMatchFound: size([pp IN scopeRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), notConfirmed: size([pp IN scopeRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND pp.userDecision = ? AND NOT pp.matchStatus = ?]), removed: size([pp IN scopeRows WHERE (pp.userDecision = ? OR coalesce(pp.archived, false))]), percentComplete: CASE WHEN size([pp IN scopeRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]) = ? THEN null ELSE toInteger(floor(?.? * size([pp IN scopeRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]) / size([pp IN scopeRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]))) END, isComplete: size([pp IN scopeRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]) > ? AND size([pp IN scopeRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]) = size([pp IN scopeRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)])}, matchedWithNew: size([pp IN scopeRows WHERE pp.matchStatus = ? AND (size(pp.pendingMatches) > ? OR size(pp.matchesExpected) > ?)]) } as progress, scopeRows as placeholders RETURN es.tildaid as scopeId, es.name as scopeName, es.scopeType as scopeType, size(placeholders) as total, progress, placeholders, COALESCE(scopeRows[?].siteName, ?) as siteName, COALESCE(scopeRows[?].siteNumber, ?) as siteNumber, CASE WHEN es.scopeType = ? THEN COALESCE(es.scopeId, ?) ELSE ? END as country
docsvc
6 253 ms 1.70 s 1.09 s flat
MATCH (d:Document {tildaid: $id}) MATCH (r:Recommendation)-[:RECOMMENDATION_TO_DOCUMENT]->(d) WHERE NOT r.category IN [?, ?, ?] MATCH (ai:AIRun)-[:AI_RUN_TO_RECOMMENDATION]->(r) MATCH (r)-[:RECOMMENDATION_TO_ORGANIZATION]->(o:Organization)…
MATCH (d:Document {tildaid: $id}) MATCH (r:Recommendation)-[:RECOMMENDATION_TO_DOCUMENT]->(d) WHERE NOT r.category IN [?, ?, ?] MATCH (ai:AIRun)-[:AI_RUN_TO_RECOMMENDATION]->(r) MATCH (r)-[:RECOMMENDATION_TO_ORGANIZATION]->(o:Organization) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (u: User) OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) OPTIONAL MATCH (dv:DocumentVersion{tildaid: d.primaryVersionId}) OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) CALL { WITH d, site OPTIONAL MATCH (study:Study{tildaid: d.studyId})<-[:PLAN_DEFINITION_TO_STUDY]- (pd:DslPlanDefinition{delpending:false, type:?})-[:PLAN_DEFINITION_TO_SITE]->(site) RETURN COALESCE(pd.siteNumber, ?) AS siteNumber } CALL { WITH dv OPTIONAL MATCH (dv)<-[:DOCUMENT_SOURCE_TO_VERSION]-(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(docAttr: DocAttribute) WITH collect([coalesce(docAttr.key, ?), coalesce(docAttr.value, ?)]) AS attributePairs WITH apoc.map.fromPairs(attributePairs) AS attributesMap WITH apoc.convert.toJson(attributesMap) AS attributesJson RETURN attributesJson } // Latest state-change ChangeLog on the version — gives us the user // who triggered the doc into its current state. Merge both event // types (Staging + StateTag) since the BE treats them as the same // documentState concept (mirrors the Python migration script). CALL { WITH dv OPTIONAL MATCH (dv)-[:ALL_CHANGELOG]-(dvcl:ChangeLog) WHERE dvcl.type IN [?, ?, ?] AND (dvcl.newValue CONTAINS ? OR dvcl.newValue CONTAINS ?) WITH dvcl ORDER BY dvcl.createdat DESC LIMIT ? OPTIONAL MATCH (changer:User {tildaid: dvcl.changedBy}) WHERE dvcl IS NOT NULL RETURN coalesce(dvcl.changedBy, ?) as statePromotedBy, coalesce(changer.name, ?) as statePromotedByName } WITH r, ai, o, d, dv, zone, section, artifact, subArtifact, u, study, site, siteNumber, attributesJson, statePromotedBy, statePromotedByName, // Document Scope key stripping: reduce the rec JSON envelope to the keys // valid for its OWN scope so irrelevant keys (e.g. country on a site rec) // don?scope?country?scope?siteName?siteId?siteNumber?scope?scope???{}?{}?????{}?{?{?{????DRecommendation????s ORIGINAL prediction (orig_data). // Same Document Scope extraction so this column lines up with // recommendedValue / actualValue. originalRecommendation: coalesce(r.orig_data, ?), recommendationCount: CASE r.category WHEN ? THEN size(keys(apoc.map.clean( apoc.convert.fromJsonMap(coalesce(r.data_formatted, ?)), [], [?]))) WHEN ? THEN apoc.coll.max([?, size([k IN keys(recMap) WHERE (recAllowed IS NULL OR k IN recAllowed) AND coalesce(toString(recMap[k]), ?) <> ?])]) ELSE ? END, actualValue: CASE r.category WHEN ? THEN coalesce(d.fileName, ?) WHEN ? THEN apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) + ? + coalesce(artifact.name, ?) WHEN ? THEN coalesce(subArtifact.name, ?) WHEN ? THEN apoc.convert.toJson( CASE toLower(coalesce(d.tmfLevel, ?)) WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), country: coalesce(d.country, ?)} WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), siteId: coalesce(site.tildaid, ?), siteName: coalesce(site.name, ?), siteNumber: siteNumber} ELSE {scope: toLower(coalesce(d.tmfLevel, ?))} END) WHEN ? THEN attributesJson ELSE ? END, // accuracyCount: for Document Metadata, the number of keys whose // recommended value matches the actual value (?–N out of // recommendationCount). For all other categories, ? or ? (mirrors // ? or ? for other categories. NULL when not yet actioned. accuracyCount: CASE r.category WHEN ? THEN size([k IN keys(apoc.map.clean(apoc.convert.fromJsonMap(coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?)), [], [?])) WHERE apoc.map.clean(apoc.convert.fromJsonMap(coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?)), [], [?])[k] = apoc.map.clean(apoc.convert.fromJsonMap(coalesce(attributesJson, ?)), [], [?])[k]]) WHEN ? THEN size([k IN keys(cmpMap) WHERE (cmpAllowed IS NULL OR k IN cmpAllowed) AND coalesce(toString(cmpMap[k]), ?) <> ? AND toLower(coalesce(toString(cmpMap[k]), ?)) = toLower(coalesce(toString((CASE toLower(coalesce(d.tmfLevel, ?)) WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), country: coalesce(d.country, ?)} WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), siteId: coalesce(site.tildaid, ?), siteName: coalesce(site.name, ?), siteNumber: siteNumber} ELSE {scope: toLower(coalesce(d.tmfLevel, ?))} END)[k]), ?))]) ELSE CASE (CASE r.category WHEN ? THEN coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?) = coalesce(d.fileName, ?) WHEN ? THEN coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?) = ( apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) + ? + coalesce(artifact.name, ?)) WHEN ? THEN coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?) = coalesce(subArtifact.name, ?) ELSE CASE WHEN r.category IN [?, ?, ?, ?] THEN r.user_action = ? OR (r.user_action = ? AND coalesce(r.comment, ?) = ?) END END) WHEN true THEN ? WHEN false THEN ? ELSE null END END, comment: r.comment, statePromotedBy: statePromotedBy, statePromotedByName: statePromotedByName } ) YIELD node RETURN node
write dashrsvc
8 218 ms 1.60 s 1.66 s flat
MATCH (doc:Document) WHERE doc.tildaid in $lists.documentIds0 WITH collect(DISTINCT doc) as documents, count(DISTINCT doc) as total UNWIND documents as doc WITH doc, total, null as vector MATCH (org:Organization{tildaid:$policyOrgId0}) CAL…
MATCH (doc:Document) WHERE doc.tildaid in $lists.documentIds0 WITH collect(DISTINCT doc) as documents, count(DISTINCT doc) as total UNWIND documents as doc WITH doc, total, null as vector MATCH (org:Organization{tildaid:$policyOrgId0}) CALL { RETURN {} AS studyById, [] AS studyIdsInScope } CALL { with org, doc WITH org, doc as doc0 match (org)<-[:DOC_TO_ORG]-(doc0:Document) RETURN doc0 as doc1, doc0 as docObj, null as docShare, true as owned, null as location union with org, doc WITH org, doc as doc0 match (org)<-[:DOC_SHARED_TO_ORG]-(docObj:DocumentShare)-[:REFERS_TO]->(doc0:Document) RETURN doc0 as doc1, docObj, docObj as docShare, false as owned, null as location union with org, doc WITH org, doc as doc0 match (:Organization {alias:?})-[:HAS_SYSTEM_TEMPLATE]->(doc0:Document) where not (doc0)-[:DOC_TO_ORG]->(org) RETURN doc0 as doc1, doc0 as docObj, null as docShare, false as owned, null as location } CALL { with org, doc1 OPTIONAL MATCH (study:Study{tildaid:doc1.studyId}) OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$pickOrgId0})-[:FOR_USER]->(:User{tildaid:$pickUserId0}) RETURN study, studyActor, [] as policies, [] as userSiteIds, [] as userCountryIds, ? as defaultAccessLevel } CALL { RETURN ? AS docAccessLevel } CALL { WITH doc1 RETURN EXISTS { (doc1)-[:HAS]-(:DocumentAnnotation)-[:ANNOTATION_SIGNER]-(:DocumentSigner{userId:$signerUserId0}) } AS isSigner } WITH total, vector, doc1 as doc, docObj, owned, study, org, isSigner, CASE WHEN $isNonMember0 AND docAccessLevel = ? THEN ? ELSE docAccessLevel END AS docAccessLevel CALL {return null as patientId, null as patientName, null as subjectId}CALL { return null as site, ? as siteNumber, [] as piNames }CALL { return null as visit }CALL { return null as user }CALL { return null as uploadedBy }CALL { return null as docType }CALL { return null as signedByUsers , null as reviewDate } WITH distinct total, doc, docObj, vector, owned, study, org, docAccessLevel, isSigner, patientId, patientName, subjectId, site, siteNumber, piNames, visit, user, uploadedBy, docType, signedByUsers, reviewDate order by doc.createdat asc , doc.tildaid asc skip $skip0 CALL { return null as workOrders, false as woContact, null as latestAIRun, null as findingCount, null as queriesCount, null as confirmedCount, null as resolvedCount }CALL { with doc OPTIONAL MATCH (doc)<-[:INCLUDES]-(b:Binder{binderType:?}) return apoc.text.join(collect (b.tag), ?) as tags } CALL { WITH doc, docObj OPTIONAL MATCH (fold:Folder{delpending:false, folderType:?})-[:INCLUDES]->(docObj) WHERE (fold.scope=? OR fold.scope=?) OPTIONAL MATCH path = (collection:Folder{level:?})-[:INCLUDES*?..?]->(fold) RETURN path, fold, collection, CASE WHEN COALESCE(doc.collection, ?) <> ? THEN [COALESCE(doc.collection, ?)] ELSE [] END as stateTags1 } CALL { WITH docObj, path, fold, collection OPTIONAL MATCH (fold)-[:HAS]->(foldFc:FolderConstraints) OPTIONAL MATCH (fold)-[:INCLUDES]->(subArtifact:SubArtifact)<-[:FILED_UNDER_SUBARTIFACT]-(docObj) OPTIONAL MATCH (subArtifact)-[:HAS]->(subFc:FolderConstraints) WITH collection, fold, subArtifact, [n IN nodes(path) | CASE WHEN n.number=? THEN n.name ELSE apoc.text.lpad(toString(COALESCE(n.number, ?)), ?, ?)+ ?+n.name END] AS nodeIds, CASE WHEN subFc IS NOT NULL THEN subFc ELSE foldFc END as folderConstraints WITH collection, fold, subArtifact, apoc.text.join(nodeIds, ?) as folderPath, folderConstraints RETURN {id: fold.tildaid, name: fold.name, number: fold.number, folderType: fold.folderType, folderScope: fold.scope, folderScopeId: fold.scopeId, path: folderPath, mandatoryFields: [], optionalFields: [], subArtifactId: COALESCE(subArtifact.tildaid, ?), subArtifactName: COALESCE(subArtifact.name, ?), constraints: CASE WHEN folderConstraints IS NOT NULL THEN folderConstraints{.*} ELSE null END} AS primaryFolder } CALL { return null as listingFolders } CALL { return null as stateTags2, null as stagingLabels } CALL { return [] as reviews} CALL { return null as templateDetails} CALL { with doc, docObj CALL { with doc, docObj with doc, docObj where doc.primaryVersionId = ? or doc.primaryVersionId is null optional match (doc)-[:UPLOADED_BY]->(uploader:User) return {tildaid: doc.tildaid, fileName: doc.fileName, originalFileName: doc.originalFileName, contentType: doc.contentType, date: doc.date, dateType: doc.dateType, versionName: doc.versionName, rejectReason: doc.rejectReason, language: doc.language, languages: doc.languages, needsAttention: doc.needsAttention, needsAttentionReasons: doc.needsAttentionReasons, trackActiveDuplicates: doc.trackActiveDuplicates, versionNumber: doc.versionNumber, majorVersion: doc.majorVersion, minorVersion: doc.minorVersion, generation: toString(doc.generation), cksum: doc.cksum, size: doc.size, status: doc.status, stateTag: doc.stateTag, pages: doc.pages, uploadMethod: doc.uploadMethod, changeLog: [], createdAt: doc.createdat, renderedCompletedAt: doc.renderedCompletedAt, renderedFieldsRevision: doc.renderedFieldsRevision, renderedSignersRevision: doc.renderedSignersRevision, uploadedBy: case when uploader is null then null else {id: uploader.tildaid, name: uploader.name, profilePicURL: uploader.profilePicURL} end} as version union with doc optional match (doc)-[:DOCUMENT_VERSION]->(dv:DocumentVersion) where $versionsSeesPreRedaction0 or coalesce(dv.preRedaction, false) = false or dv.tildaid = doc.primaryVersionId optional match (doc)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:DOCUMENT_SOURCE_TO_VERSION]->(dv) optional match (dv)-[:UPLOADED_BY]->(uploader:User) optional match (dv)-[:PROMOTED_FROM]->(psrc:Document) return case when dv is null then null else {tildaid: dv.tildaid, fileName: dv.fileName, originalFileName: dv.originalFileName, contentType: dv.contentType, date: dv.date, dateType: doc.dateType, versionName: dv.versionName, rejectReason: dv.rejectReason, language: dv.language, languages: dv.languages, needsAttention: dv.needsAttention, needsAttentionReasons: dv.needsAttentionReasons, trackActiveDuplicates: dv.trackActiveDuplicates, versionNumber: dv.versionNumber, majorVersion: dv.majorVersion, minorVersion: dv.minorVersion, documentSourceId: ds.tildaid, promotedFromDocumentId: psrc.tildaid, generation: toString(dv.generation), linearizedPdfUrl: dv.linearizedPdfUrl, linearizedPdfStatus: dv.linearizedPdfStatus, needsProcessedPdf: dv.needsProcessedPdf, langDetectionStatus: dv.langDetectionStatus, translatedPdfUrl: dv.translatedPdfUrl, translatedPdfStatus: dv.translatedPdfStatus, cksum: dv.cksum, size: dv.size, status: dv.status, stateTag: dv.stateTag, pages: dv.pages, uploadMethod: dv.uploadMethod, changeLog: [], url: dv.url, createdAt: dv.createdat, renderedCompletedAt: dv.renderedCompletedAt, renderedFieldsRevision: dv.renderedFieldsRevision, renderedSignersRevision: dv.renderedSignersRevision, preRedaction: coalesce(dv.preRedaction, false), uploadedBy: case when uploader is null then null else {id: uploader.tildaid, name: uploader.name, profilePicURL: uploader.profilePicURL} end} end as version // TODO: [Major/Minor Versioning] Once client supports minor version UX, update ordering to: // } with doc, version order by version.majorVersion, version.minorVersion // This will display versions as ?.?, ?.?, ?.? instead of flat ?, ?, ? } with doc, version order by version.versionNumber where $versionsVersionId0 IS NULL OR version.tildaid = $versionsVersionId0 return collect(version) as versions } CALL { return null as annotationSpec, null as annotationVal } CALL { return [] as queries } CALL { WITH docObj, owned OPTIONAL MATCH (docObj)<-[:REFERS_TO]-(docShare:DocumentShare)-[:DOC_SHARED_TO_ORG]->(otherOrg:Organization) WHERE owned CALL { WITH docShare, docObj OPTIONAL MATCH (docShare)-[signedByRel:SIGNED_BY]->(signedBy:User) WHERE (docObj.docKind = ? OR coalesce(signedByRel.majorVersion, docObj.majorVersion, ?) = coalesce(head([(docObj)-[:DOCUMENT_VERSION]->(vv:DocumentVersion{tildaid: $reviewedVersionId0}) | vv.majorVersion]), docObj.majorVersion, ?)) WITH COLLECT(DISTINCT({id: signedBy.tildaid, name: signedBy.name, profilePicURL: signedBy.profilePicURL})) as signedByUsers, count(signedBy) as signersCount, max(signedByRel.createdat) as reviewDate RETURN CASE WHEN signersCount<>? THEN signedByUsers ELSE [] END as signedByUsers, reviewDate } CALL { WITH docShare OPTIONAL MATCH (docShare)-[:ALL_CHANGELOG]->(c:ChangeLog) WHERE c.level IS NULL OR c.level = ? OPTIONAL MATCH (e:ESignature{tildaid:c.blob}) WITH CASE WHEN NOT c is NULL THEN {changeLog: c{.*}, eSignature: e{.*}} END as clog order by c.createdat asc RETURN collect(clog) as changeLog } WITH CASE WHEN NOT otherOrg is NULL THEN {readerOrg: otherOrg.name, readerOrgId: otherOrg.tildaid, readerOrgAlias: otherOrg.alias, status: docShare.ownerStatus, signedByUsers: signedByUsers, reviewDate: reviewDate, changeLog: changeLog} END as shareEntry RETURN collect(shareEntry) as sharedWith } CALL { WITH docObj RETURN CASE WHEN (docObj)-[:REFERS_TO]->(:Document) THEN docObj{.*} ELSE null END as currentShare } CALL { WITH doc MATCH (doc)-[:DOCUMENT_VERSION]->(dv:DocumentVersion{tildaid: coalesce($docAttrVersionId0, doc.primaryVersionId)}) MATCH (doc)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:DOCUMENT_SOURCE_TO_VERSION]->(dv) OPTIONAL MATCH (ds)-[:HAS_ATTRIBUTE]->(da:DocAttribute) OPTIONAL MATCH (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) RETURN collect(DISTINCT da{.*, name: COALESCE(dad.name, ?), description: COALESCE(dad.description, ?), documentSourceId: ds.tildaid}) as docAttributes } CALL { return null as notesCount } CALL { return null as eventId, null as eventName, null as deliverable, null as eventScope } CALL { return null as copiedFrom } CALL { return [] as copiedTo, ? as copiedToCount } CALL { return null as promotedTo } CALL { return null AS docReviewStatus, null AS hasImportantComment, null AS discrepancyCount } CALL { WITH docObj OPTIONAL MATCH (docObj)<-[:INCLUDES]-(currentFolder:Folder{folderType:?}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (docObj)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (docObj)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) WITH CASE WHEN stagingInfo IS NOT NULL THEN {artifactId: stagingInfo.folderId, subArtifactId: stagingInfo.subArtifactId, stagingInfoId: stagingInfo.tildaid} WHEN currentFolder.level = ? THEN {artifactId: currentFolder.tildaid, subArtifactId: COALESCE(currentSubArtifact.tildaid, ?), stagingInfoId: ?} ELSE {artifactId: ?, subArtifactId: ?, stagingInfoId: ?} END as tmfInfo WITH {folderId: tmfInfo.artifactId, subArtifactId: tmfInfo.subArtifactId, stagingInfoId: tmfInfo.stagingInfoId} as tmfCategorization RETURN CASE WHEN tmfCategorization.folderId=? THEN null ELSE tmfCategorization END as tmfCategorization } with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total return document, total
docsvc
9 220 ms 1.55 s 1.90 s flat
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DE…
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DEFINITION_TO_SITE]->(s:Site) MATCH (a)-[:FOR_USER]->(u:User) WHERE a.state = $active AND u.status IN [$statusActive, $statusInvited] AND a.orgId = s.orgId RETURN DISTINCT u.email, s.name AS siteName, st.name AS studyName
tusker
18 196 ms 1.55 s 1.13 s flat
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DE…
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DEFINITION_TO_SITE]->(s:Site) MATCH (a)-[:FOR_USER]->(u:User) WHERE a.state = $active AND u.status IN [$statusActive, $statusInvited] AND a.orgId = s.orgId RETURN DISTINCT u.email, s.name AS siteName, st.name AS studyName
tusker
42 166 ms 990 ms 1.23 s flat
MATCH (o:Study{tildaid:$scopeId})-[scope:STUDY_TO_ACTOR]->(a:Actor{delpending:false})-[:FOR_USER]->(u:User) WHERE u.status<>? and a.state<>? MATCH (a)-[:APPOINTED_AS]->(r:Role) WHERE r.name <> ? MATCH (filterSite:Site{orgId:a.orgId}) WHERE…
MATCH (o:Study{tildaid:$scopeId})-[scope:STUDY_TO_ACTOR]->(a:Actor{delpending:false})-[:FOR_USER]->(u:User) WHERE u.status<>? and a.state<>? MATCH (a)-[:APPOINTED_AS]->(r:Role) WHERE r.name <> ? MATCH (filterSite:Site{orgId:a.orgId}) WHERE filterSite.tildaid in $filterSiteIds AND EXISTS { MATCH (a)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(spd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(filterSite), (spd)-[:PLAN_DEFINITION_TO_STUDY]-(o) } optional match (co:ClinOp{deltimestamp:?, orgId:$orgId})-[:FOR_USER]-(u) with distinct o,a,r,u,scope,co where ( toString(u.status) = $actorFilter0 ) with o,a,u,scope,r,co order by r.displayName with o,a,u,scope,co,collect(distinct case when r.listed = true then r.displayName end) as displayRoles with o,a,u,scope,displayRoles,co order by a.createdat, a.tildaid limit $qpLimit RETURN apoc.create.vNode([?], { id: a.tildaid, role: a.roles, displayRoles: CASE WHEN size(displayRoles) = ? THEN [?] ELSE displayRoles END, state: a.state, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, userEmail: case when coalesce(co.actorEmail,?)<>? then co.actorEmail else u.email end, userId: u.tildaid, userName: case when coalesce(co.actorName,?)<>? then co.actorName else coalesce(u.name, u.email) end, userContact: case when coalesce(co.actorPhone,?)<>? then co.actorPhone else coalesce(u.contact,?) end, lastActivity: u.lastActivity, profilePicURL: u.profilePicURL, userTitle: u.title, createdat: scope.createdat, isBlinded: a.isBlinded , capabilities: scope.capabilities, applyToStudyRoleFlag: scope.applyToStudyRoleFlag }) as node
write execsvc
1 662 ms 975 ms 662 ms flat
MATCH (n:Folder|SubArtifact) WHERE n.isArchived IS NULL SET n.isArchived = false RETURN count(n)
MATCH (n:Folder|SubArtifact) WHERE n.isArchived IS NULL SET n.isArchived = false RETURN count(n)
write docsvc
1 713 ms 975 ms 713 ms flat
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- …
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]- (pd:DslPlanDefinition) MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)-[:SITE_TO_SMO]- (smo)-[:IS_AN_ORG]-(org:Organization) OPTIONAL MATCH (visit:VisitV2)-[:BELONGS_TO_VISIT]-(pv) WITH pv, cp, st, site, org, visit OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(emptyPa:ParticipantActivity) WHERE (emptyPa.edcStatus IS NULL OR emptyPa.edcStatus = ?) AND emptyPa.deltimestamp = ? WITH pv, cp, st, site, org, visit, collect(emptyPa) AS emptyPas OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity)-[:BELONGS_TO_ACTIVITY]->(act:ActivityV2) WHERE pa.deltimestamp = ? RETURN pv, cp, st, site, org, visit, emptyPas, collect({pa: pa, activity: act}) AS paActPairs LIMIT ?
execsvc
7 607 ms 975 ms 679 ms flat
MATCH (study:Study {tildaid: $studyId})-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) CALL { WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:P…
MATCH (study:Study {tildaid: $studyId})-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) CALL { WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl)-[:PARTICIPANT_CONTROL]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw UNION ALL WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw } WITH DISTINCT study, t, ptc, pw MATCH (t)-[:BELONGS_TO]-(:Inbox)-[:MANAGED_BY]-(o:Organization) MATCH (ptc)-[:BELONGS_TO_TASKCONFIG]-(tc:TaskConfig) OPTIONAL MATCH (t)-[:BELONGS_TO]-(taskInbox:Inbox{type:?})--(taskUser:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:Actor{delpending:false})-[:FOR_USER]-(user:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(scopeSite:Site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(o) WITH DISTINCT study, t, o, tc, taskInbox, taskUser, user, scopeSite SKIP $skip LIMIT $limit WITH collect({study:study, t:t, o:o, tc:tc, taskInbox:taskInbox, taskUser:taskUser, user:user, scopeSite:scopeSite}) AS rows, collect(DISTINCT study) AS studies, collect(DISTINCT o) AS orgs, collect(DISTINCT scopeSite) AS sites CALL { WITH studies UNWIND studies AS study OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(r:Role) WHERE r.name IN [?, ?, ?] RETURN study.tildaid AS studyId, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS piTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS smoTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS raTildaid } WITH rows, orgs, sites, collect({studyId: studyId, pi: piTildaid, smo: smoTildaid, ra: raTildaid}) AS studyRoles CALL { WITH orgs UNWIND orgs AS o OPTIONAL MATCH (o)-[:ORGANIZATION_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN o.tildaid AS orgId, collect(DISTINCT u.tildaid) AS adminTildaid } WITH rows, sites, studyRoles, collect({orgId: orgId, admin: adminTildaid}) AS orgRoles CALL { WITH sites UNWIND (CASE WHEN size(sites) = ? THEN [null] ELSE sites END) AS scopeSite OPTIONAL MATCH (scopeSite)-[:STAFF_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN scopeSite.tildaid AS siteId, collect(DISTINCT u.tildaid) AS siteAdminTildaid } WITH rows, studyRoles, orgRoles, collect({siteId: siteId, admin: siteAdminTildaid}) AS siteRoles UNWIND rows AS row WITH row, [x IN studyRoles WHERE x.studyId = row.study.tildaid][?] AS sr, [x IN orgRoles WHERE x.orgId = row.o.tildaid][?] AS orr, [x IN siteRoles WHERE row.scopeSite IS NOT NULL AND x.siteId = row.scopeSite.tildaid][?] AS str RETURN DISTINCT row.t AS task, apoc.convert.toJson({userTildaid: row.user.tildaid, userName: row.user.name, PITildaid: sr.pi, studyOwnerTildaid: sr.smo, regAnalystTildaid: sr.ra, orgAdminTildaid: orr.admin, studyName: row.study.name, siteName: row.scopeSite.name, siteAdminTildaid: CASE WHEN row.scopeSite IS NULL THEN [] ELSE str.admin END}) AS details, row.tc.who AS dsnWho, row.o.alias AS orgAlias, row.taskInbox, row.taskUser.tildaid AS taskUserId
tusker
1 617 ms 975 ms 617 ms flat
UNWIND $userIds AS uid CALL (uid) { MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{ownerOrgId:$orgId, deltimestamp:?}) WHERE coalesce(ta.studyId, ?) = ? AND (ta.state = $assignedState OR EXISTS { (ta)-[:P…
UNWIND $userIds AS uid CALL (uid) { MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{ownerOrgId:$orgId, deltimestamp:?}) WHERE coalesce(ta.studyId, ?) = ? AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta) WHERE (coalesce(training.scopeType, ?) = $orgScopeType) AND (training.orgId <> ? AND training.orgId = $orgId) RETURN ta, training, null AS study UNION MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{deltimestamp:?}) WHERE ta.studyId IN $studyIds AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta), (training)-[:PLAN_DEFINITION_TO_STUDY]->(study:Study{delpending:false}) WHERE study.tildaid = ta.studyId AND (NOT ((coalesce(training.siteScope, ?) = $studyTeamScope) OR (coalesce(training.scopeType, ?) = $orgScopeType)) OR (training.orgId <> ? AND training.orgId = $callerOrgId)) RETURN ta, training, study } CALL (ta) { OPTIONAL MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(trainingPA:ParticipantActivity{deltimestamp:?}) WHERE CASE WHEN ta.state = $assignedState THEN NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY]->() } ELSE trainingPA.status IN $completedStatuses AND NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY*]->(newer:ParticipantActivity{deltimestamp:?}) WHERE newer.status IN $completedStatuses } END RETURN trainingPA ORDER BY trainingPA.createdat DESC LIMIT ? } WITH uid, training, ta, study, trainingPA WHERE trainingPA IS NOT NULL MATCH (trainingPA)-[:BELONGS_TO_ACTIVITY]->(trainingActivity:ActivityV2) CALL (trainingPA, trainingActivity) { OPTIONAL MATCH (trainingPA)-[:PARTICIPANT_QUESTIONNAIRE]-(tpPqnr:ParticipantQuestionnaire)-[:BELONGS_TO_QUESTIONNAIRE]-(tpQnr:QuestionnaireV2) WHERE exists((tpQnr)-[:GRAPH_TO_VARIABLE {graphid: tpQnr.tildaid, path: [tpQnr.tildaid]}]->(:VariableV2 {name: ?, type: ?})) WITH trainingPA, trainingActivity, tpPqnr IS NOT NULL AS tpIsQuiz, tpQnr.title AS tpTitle, tpPqnr.attempts AS tpAttempts, tpPqnr.gradedQuestionCount AS tpGraded, tpPqnr.correctQuestionCount AS tpCorrect, [(tpPqnr)-[:PARTICIPANT_VARIABLE]-(tpPv:ParticipantVariable)-[:BELONGS_TO_VARIABLE]-(tpVar:VariableV2) WHERE tpVar.name IN [?, ?, ?] | [tpVar.name, tpPv.value]] AS tpVals WITH trainingPA, trainingActivity, count(CASE WHEN any(x IN tpVals WHERE x[?] = ? AND coalesce(x[?], ?) <> ?) THEN ? END) AS tpFailed, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotal, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotalMax, collect(CASE WHEN tpIsQuiz THEN {title: tpTitle, attempts: coalesce(tpAttempts, ?), graded: tpGraded, correct: tpCorrect, score: head([x IN tpVals WHERE x[?] = ? | x[?]]), max: head([x IN tpVals WHERE x[?] = ? | x[?]]), result: head([x IN tpVals WHERE x[?] = ? | x[?]])} END) AS tpQuizzes WITH trainingPA, trainingActivity, tpFailed, tpTotal, tpTotalMax, tpQuizzes, [q IN tpQuizzes | q.score] AS tpScores, [q IN tpQuizzes | q.max] AS tpMaxScores RETURN tpFailed = ? AND (trainingPA.status = ? OR coalesce(trainingActivity.passingScore, ?) = ? OR tpTotalMax = ? OR tpTotal * ? >= coalesce(trainingActivity.passingScore, ?) * tpTotalMax) AS trainingPassed, tpScores AS trainingScoreValues, tpMaxScores AS trainingMaxScoreValues } WITH uid, training, ta, study, trainingActivity, trainingPA, trainingPassed, trainingScoreValues, trainingMaxScoreValues, [(trainingPA)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE cl.type = ? AND cl.newValue IN [?,?,?,?] | [cl.newValue, cl.createdat, cl.who, CASE WHEN cl.newValue = ? AND cl.blob STARTS WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END, cl.reason]] AS trainingLog ORDER BY training.name, training.tildaid RETURN uid, collect({activityId:trainingActivity.tildaid, activityTitle:trainingActivity.title, version:trainingActivity.version, paId:trainingPA.tildaid, paStatus:trainingPA.status, paUpdatedAt:trainingPA.updatedat, paCreatedAt:trainingPA.createdat, paDueDate:trainingPA.dueDate, trainingId:training.tildaid, trainingName:training.name, trainingStatus:training.status, validUntil:training.validUntil, validUntilUnits:training.validUntilUnits, assignmentState:ta.state, studyId:study.tildaid, studyName:study.name, passed:trainingPassed, scoreValues:trainingScoreValues, maxScoreValues:trainingMaxScoreValues, assignments:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]], completions:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]] }) AS trainingActivities
execsvc
1 815 ms 975 ms 815 ms flat
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- …
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]- (pd:DslPlanDefinition) MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)-[:SITE_TO_SMO]- (smo)-[:IS_AN_ORG]-(org:Organization) OPTIONAL MATCH (visit:VisitV2)-[:BELONGS_TO_VISIT]-(pv) WITH pv, cp, st, site, org, visit OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(emptyPa:ParticipantActivity) WHERE (emptyPa.edcStatus IS NULL OR emptyPa.edcStatus = ?) AND emptyPa.deltimestamp = ? WITH pv, cp, st, site, org, visit, collect(emptyPa) AS emptyPas OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity)-[:BELONGS_TO_ACTIVITY]->(act:ActivityV2) WHERE pa.deltimestamp = ? RETURN pv, cp, st, site, org, visit, emptyPas, collect({pa: pa, activity: act}) AS paActPairs LIMIT ?
execsvc
1 633 ms 975 ms 633 ms flat
UNWIND $userIds AS uid CALL (uid) { MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{ownerOrgId:$orgId, deltimestamp:?}) WHERE coalesce(ta.studyId, ?) = ? AND (ta.state = $assignedState OR EXISTS { (ta)-[:P…
UNWIND $userIds AS uid CALL (uid) { MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{ownerOrgId:$orgId, deltimestamp:?}) WHERE coalesce(ta.studyId, ?) = ? AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta) WHERE (coalesce(training.scopeType, ?) = $orgScopeType) AND (training.orgId <> ? AND training.orgId = $orgId) RETURN ta, training, null AS study UNION MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{deltimestamp:?}) WHERE ta.studyId IN $studyIds AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta), (training)-[:PLAN_DEFINITION_TO_STUDY]->(study:Study{delpending:false}) WHERE study.tildaid = ta.studyId AND (NOT ((coalesce(training.siteScope, ?) = $studyTeamScope) OR (coalesce(training.scopeType, ?) = $orgScopeType)) OR (training.orgId <> ? AND training.orgId = $callerOrgId)) RETURN ta, training, study } CALL (ta) { OPTIONAL MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(trainingPA:ParticipantActivity{deltimestamp:?}) WHERE CASE WHEN ta.state = $assignedState THEN NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY]->() } ELSE trainingPA.status IN $completedStatuses AND NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY*]->(newer:ParticipantActivity{deltimestamp:?}) WHERE newer.status IN $completedStatuses } END RETURN trainingPA ORDER BY trainingPA.createdat DESC LIMIT ? } WITH uid, training, ta, study, trainingPA WHERE trainingPA IS NOT NULL MATCH (trainingPA)-[:BELONGS_TO_ACTIVITY]->(trainingActivity:ActivityV2) CALL (trainingPA, trainingActivity) { OPTIONAL MATCH (trainingPA)-[:PARTICIPANT_QUESTIONNAIRE]-(tpPqnr:ParticipantQuestionnaire)-[:BELONGS_TO_QUESTIONNAIRE]-(tpQnr:QuestionnaireV2) WHERE exists((tpQnr)-[:GRAPH_TO_VARIABLE {graphid: tpQnr.tildaid, path: [tpQnr.tildaid]}]->(:VariableV2 {name: ?, type: ?})) WITH trainingPA, trainingActivity, tpPqnr IS NOT NULL AS tpIsQuiz, tpQnr.title AS tpTitle, tpPqnr.attempts AS tpAttempts, tpPqnr.gradedQuestionCount AS tpGraded, tpPqnr.correctQuestionCount AS tpCorrect, [(tpPqnr)-[:PARTICIPANT_VARIABLE]-(tpPv:ParticipantVariable)-[:BELONGS_TO_VARIABLE]-(tpVar:VariableV2) WHERE tpVar.name IN [?, ?, ?] | [tpVar.name, tpPv.value]] AS tpVals WITH trainingPA, trainingActivity, count(CASE WHEN any(x IN tpVals WHERE x[?] = ? AND coalesce(x[?], ?) <> ?) THEN ? END) AS tpFailed, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotal, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotalMax, collect(CASE WHEN tpIsQuiz THEN {title: tpTitle, attempts: coalesce(tpAttempts, ?), graded: tpGraded, correct: tpCorrect, score: head([x IN tpVals WHERE x[?] = ? | x[?]]), max: head([x IN tpVals WHERE x[?] = ? | x[?]]), result: head([x IN tpVals WHERE x[?] = ? | x[?]])} END) AS tpQuizzes WITH trainingPA, trainingActivity, tpFailed, tpTotal, tpTotalMax, tpQuizzes, [q IN tpQuizzes | q.score] AS tpScores, [q IN tpQuizzes | q.max] AS tpMaxScores RETURN tpFailed = ? AND (trainingPA.status = ? OR coalesce(trainingActivity.passingScore, ?) = ? OR tpTotalMax = ? OR tpTotal * ? >= coalesce(trainingActivity.passingScore, ?) * tpTotalMax) AS trainingPassed, tpScores AS trainingScoreValues, tpMaxScores AS trainingMaxScoreValues } WITH uid, training, ta, study, trainingActivity, trainingPA, trainingPassed, trainingScoreValues, trainingMaxScoreValues, [(trainingPA)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE cl.type = ? AND cl.newValue IN [?,?,?,?] | [cl.newValue, cl.createdat, cl.who, CASE WHEN cl.newValue = ? AND cl.blob STARTS WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END, cl.reason]] AS trainingLog ORDER BY training.name, training.tildaid RETURN uid, collect({activityId:trainingActivity.tildaid, activityTitle:trainingActivity.title, version:trainingActivity.version, paId:trainingPA.tildaid, paStatus:trainingPA.status, paUpdatedAt:trainingPA.updatedat, paCreatedAt:trainingPA.createdat, paDueDate:trainingPA.dueDate, trainingId:training.tildaid, trainingName:training.name, trainingStatus:training.status, validUntil:training.validUntil, validUntilUnits:training.validUntilUnits, assignmentState:ta.state, studyId:study.tildaid, studyName:study.name, passed:trainingPassed, scoreValues:trainingScoreValues, maxScoreValues:trainingMaxScoreValues, assignments:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]], completions:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]] }) AS trainingActivities
execsvc
1 643 ms 975 ms 643 ms flat
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DE…
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DEFINITION_TO_SITE]->(s:Site) MATCH (a)-[:FOR_USER]->(u:User) WHERE a.state = $active AND u.status IN [$statusActive, $statusInvited] AND a.orgId = s.orgId RETURN DISTINCT u.email, s.name AS siteName, st.name AS studyName
tusker
1 811 ms 975 ms 811 ms flat
MATCH (o:Organization{tildaid:$id})-[:USES]->(fm:FolderManager{tildaid:$id1}) OPTIONAL MATCH (fm)-[:INCLUDES]->(f0:Folder{folderType:?}) OPTIONAL MATCH (f0)-[:INCLUDES]->(f1:Folder) OPTIONAL MATCH (f1)-[:INCLUDES]->(f2:Folder) OPTIONAL MAT…
MATCH (o:Organization{tildaid:$id})-[:USES]->(fm:FolderManager{tildaid:$id1}) OPTIONAL MATCH (fm)-[:INCLUDES]->(f0:Folder{folderType:?}) OPTIONAL MATCH (f0)-[:INCLUDES]->(f1:Folder) OPTIONAL MATCH (f1)-[:INCLUDES]->(f2:Folder) OPTIONAL MATCH (f2)-[:INCLUDES]->(f3:Folder) OPTIONAL MATCH (f3)-[:INCLUDES]->(sa:SubArtifact) OPTIONAL MATCH (f3)-[:HAS]->(f3Constraint:FolderConstraints) OPTIONAL MATCH (sa)-[:HAS]->(saConstraint:FolderConstraints) OPTIONAL MATCH (f3Constraint)-[:HAS_ATTRIBUTE_CONSTRAINT]->(f3Dac:DocAttributeConstraint) OPTIONAL MATCH (saConstraint)-[:HAS_ATTRIBUTE_CONSTRAINT]->(saDac:DocAttributeConstraint) WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, f3Dac, saDac WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, COLLECT(DISTINCT CASE WHEN f3Dac IS NOT NULL THEN {id: f3Dac.tildaid, key: f3Dac.key, order: f3Dac.order, description: f3Dac.description, isMandatory: f3Dac.isMandatory} ELSE NULL END) as f3Constraints, COLLECT(DISTINCT CASE WHEN saDac IS NOT NULL THEN {id: saDac.tildaid, key: saDac.key, order: saDac.order, description: saDac.description, isMandatory: saDac.isMandatory} ELSE NULL END) as saConstraints WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, COUNT(sa) as saNodeCount, [attr IN f3Constraints WHERE attr IS NOT NULL] as f3ConstraintsFiltered, [attr IN saConstraints WHERE attr IS NOT NULL] as saConstraintsFiltered UNWIND CASE WHEN size(f3ConstraintsFiltered) > ? THEN f3ConstraintsFiltered ELSE [null] END as f3Attr WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, saNodeCount, saConstraintsFiltered, f3Attr WHERE f3Attr IS NOT NULL OR size(f3ConstraintsFiltered) = ? WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, saNodeCount, saConstraintsFiltered, f3Attr ORDER BY COALESCE(f3Attr.order, ?) WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, saNodeCount, saConstraintsFiltered, [attr IN COLLECT(f3Attr) WHERE attr IS NOT NULL] as f3ConstraintList UNWIND CASE WHEN size(saConstraintsFiltered) > ? THEN saConstraintsFiltered ELSE [null] END as saAttr WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, saNodeCount, f3ConstraintList, saAttr WHERE saAttr IS NOT NULL OR size(saConstraintsFiltered) = ? WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, saNodeCount, f3ConstraintList, saAttr ORDER BY COALESCE(saAttr.order, ?) WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, saNodeCount, f3ConstraintList, [attr IN COLLECT(saAttr) WHERE attr IS NOT NULL] as saConstraintList WITH f0, f1, f2, f3, f3Constraint, sa, saConstraint, saNodeCount, f3ConstraintList, saConstraintList, {name: sa.name, id: sa.tildaid, parentFolderId: f3.tildaid, number: sa.number, description: sa.description, aiInstructions: CASE WHEN $includeAIInstructions THEN sa.aiInstructions ELSE null END, risk: sa.risk, isArchived: sa.isArchived, constraints: CASE WHEN saConstraint IS NOT NULL THEN { id: saConstraint.tildaid, mandatory: saConstraint.mandatory, tmfLevel: saConstraint.tmfLevel, attributeConstraints: CASE WHEN size(saConstraintList) > ? THEN saConstraintList ELSE [] END } ELSE NULL END} as saNode ORDER BY toLower(saNode.name) WITH f0, f1, f2, f3, f3Constraint, saNodeCount, f3ConstraintList, COLLECT(saNode) as saNodes WITH f0, f1, f2, COUNT(f3) as f3NodeCount, {name: f3.name, number: f3.number, id: f3.tildaid, parentFolderId: f2.tildaid, level: f3.level, description: f3.description, aiInstructions: CASE WHEN $includeAIInstructions THEN f3.aiInstructions ELSE null END, risk: f3.risk, scope: f3.scope, type: f3.folderType, folderManagerId: f3.folderManagerId, doctypes: f3.doctypes, isInternal: f3.isInternal, isArchived: f3.isArchived, path: apoc.text.join([f0.name, apoc.text.lpad(toString(COALESCE(f1.number, ?)), ?, ?)+ ?+f1.name, apoc.text.lpad(toString(COALESCE(f1.number, ?)), ?, ?)+ ?+ apoc.text.lpad(toString(COALESCE(f2.number, ?)), ?, ?)+ ?+f2.name, apoc.text.lpad(toString(COALESCE(f1.number, ?)), ?, ?)+ ?+ apoc.text.lpad(toString(COALESCE(f2.number, ?)), ?, ?)+ ?+apoc.text.lpad(toString(COALESCE(f3.number, ?)), ?, ?)+ ?+f3.name], ?), folders:[], documents: [], mandatoryFields: [], optionalFields: [], subArtifacts: CASE WHEN saNodeCount=? THEN [] ELSE saNodes END, constraints: CASE WHEN f3Constraint IS NOT NULL THEN { id: f3Constraint.tildaid, mandatory: f3Constraint.mandatory, tmfLevel: f3Constraint.tmfLevel, attributeConstraints: CASE WHEN size(f3ConstraintList) > ? THEN f3ConstraintList ELSE [] END } ELSE NULL END} as f3Node ORDER BY f3Node.number WITH f0, f1, f2, f3NodeCount, COLLECT(f3Node) as f3Nodes WITH f0, f1, COUNT(f2) as f2NodeCount, {name: f2.name, number: f2.number, id: f2.tildaid, parentFolderId: f1.tildaid, level: f2.level, description: f2.description, aiInstructions: CASE WHEN $includeAIInstructions THEN f2.aiInstructions ELSE null END, risk: f2.risk, scope: f2.scope, type: f2.folderType, folderManagerId: f2.folderManagerId, doctypes: f2.doctypes, isInternal: f2.isInternal, isArchived: f2.isArchived, path: apoc.text.join([f0.name, apoc.text.lpad(toString(COALESCE(f1.number, ?)), ?, ?)+ ?+f1.name, apoc.text.lpad(toString(COALESCE(f1.number, ?)), ?, ?)+ ?+ apoc.text.lpad(toString(COALESCE(f2.number, ?)), ?, ?)+ ?+f2.name], ?), folders: CASE WHEN f3NodeCount=? THEN [] ELSE f3Nodes END, documents: [], mandatoryFields: [], optionalFields: [], subArtifacts: []} as f2Node ORDER BY f2Node.number WITH f0, f1, f2NodeCount, COLLECT(f2Node) as f2Nodes WITH f0, f1, COUNT(f1) as f1NodeCount, {name: f1.name, number: f1.number, id: f1.tildaid, parentFolderId: f0.tildaid, level: f1.level, description: f1.description, aiInstructions: CASE WHEN $includeAIInstructions THEN f1.aiInstructions ELSE null END, risk: f1.risk, scope: f1.scope, type: f1.folderType, folderManagerId: f1.folderManagerId, doctypes: f1.doctypes, isInternal: f1.isInternal, isArchived: f1.isArchived, path: apoc.text.join([f0.name, apoc.text.lpad(toString(COALESCE(f1.number, ?)), ?, ?)+ ?+f1.name], ?), folders: CASE WHEN f2NodeCount=? THEN [] ELSE f2Nodes END, documents: [], mandatoryFields: [], optionalFields: [], subArtifacts: []} as f1Node ORDER BY f1Node.number WITH f0, f1NodeCount, COLLECT(f1Node) as f1Nodes RETURN DISTINCT { name: f0.name, number: f0.number, id: f0.tildaid, level: f0.level, description: f0.description, aiInstructions: CASE WHEN $includeAIInstructions THEN f0.aiInstructions ELSE null END, risk: f0.risk, scope: f0.scope, type: f0.folderType, folderManagerId: f0.folderManagerId, doctypes: f0.doctypes, isInternal: f0.isInternal, isArchived: f0.isArchived, path: f0.name, folders: CASE WHEN f1NodeCount=? THEN [] ELSE f1Nodes END, mandatoryFields: [], optionalFields: [], documents: [], subArtifacts: [] } as f0Node ORDER BY toLower(f0Node.name)
docsvc
1 520 ms 975 ms 520 ms flat
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {de…
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)<-[:FOR_USER]-(da:Actor {delpending: false})<-[:STUDY_TO_DELEGATED_ACTOR]-(dst:Study {delpending: false})-[:STUDY_TO_ORG]->(o:Organization {delpending: false}), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor {delpending: false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias: $tildaScaleAlias}) WHERE dst.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) RETURN o UNION WITH u MATCH (o:Organization {alias: $activeAlias, delpending: false}) RETURN o } OPTIONAL MATCH (st:Study {delpending: false})-[:STUDY_TO_ORG]->(o) RETURN collect(DISTINCT o.alias) AS aliases, collect(DISTINCT o.tildaid) AS orgIds, collect(DISTINCT st.tildaid) AS studyIds
dashql
1 748 ms 975 ms 748 ms flat
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {de…
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)<-[:FOR_USER]-(da:Actor {delpending: false})<-[:STUDY_TO_DELEGATED_ACTOR]-(dst:Study {delpending: false})-[:STUDY_TO_ORG]->(o:Organization {delpending: false}), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor {delpending: false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias: $tildaScaleAlias}) WHERE dst.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) RETURN o UNION WITH u MATCH (o:Organization {alias: $activeAlias, delpending: false}) RETURN o } OPTIONAL MATCH (st:Study {delpending: false})-[:STUDY_TO_ORG]->(o) RETURN collect(DISTINCT o.alias) AS aliases, collect(DISTINCT o.tildaid) AS orgIds, collect(DISTINCT st.tildaid) AS studyIds
dashql
4 679 ms 967 ms 907 ms flat
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {de…
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)<-[:FOR_USER]-(da:Actor {delpending: false})<-[:STUDY_TO_DELEGATED_ACTOR]-(dst:Study {delpending: false})-[:STUDY_TO_ORG]->(o:Organization {delpending: false}), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor {delpending: false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias: $tildaScaleAlias}) WHERE dst.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) RETURN o UNION WITH u MATCH (o:Organization {alias: $activeAlias, delpending: false}) RETURN o } OPTIONAL MATCH (st:Study {delpending: false})-[:STUDY_TO_ORG]->(o) RETURN collect(DISTINCT o.alias) AS aliases, collect(DISTINCT o.tildaid) AS orgIds, collect(DISTINCT st.tildaid) AS studyIds
dashql
4 450 ms 967 ms 597 ms flat
MATCH (study:Study)<-[:PLAN_CONTEXT]-(:EventPlan {orgId: $orgId})-[:EVENTS]->(e:Event) WHERE coalesce(e.isTemplate, false) = false AND study.tildaid in $studyIds WITH DISTINCT e, study CALL { WITH e, study // Get details for each scope in …
MATCH (study:Study)<-[:PLAN_CONTEXT]-(:EventPlan {orgId: $orgId})-[:EVENTS]->(e:Event) WHERE coalesce(e.isTemplate, false) = false AND study.tildaid in $studyIds WITH DISTINCT e, study CALL { WITH e, study // Get details for each scope in the event OPTIONAL MATCH (e)<-[:PART_OF_EVENT]-(scope:EventScope) WHERE scope.status IN $scopeStatuses AND ($scopeType = ? OR scope.scopeType = $scopeType) OPTIONAL MATCH (site:Site {tildaid: scope.scopeId}) OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition{type:?})-[:PLAN_DEFINITION_TO_STUDY]->(study) WITH scope, CASE WHEN scope.scopeType = ? THEN site.name ELSE ? END as siteName, CASE WHEN scope.scopeType = ? THEN COALESCE(pd.siteNumber, ?) ELSE ? END as siteNumber OPTIONAL MATCH (scope)-[:APPLICABLE_TO]->(scopeDeliverable:Deliverable) CALL { // Get details of each deliverable applicable to the scope WITH scope, scopeDeliverable OPTIONAL MATCH (scopeDeliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document)-[:SCOPED_TO]->(scope) CALL { // Get match status for each placeholder WITH ph, tracksRel, scope OPTIONAL MATCH (ph)-[acceptedRel:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) OPTIONAL MATCH (ph)-[matchRel:MATCHING_DOCUMENT]->(matchDoc:Document) WHERE acceptedDoc IS NULL OR matchDoc.tildaid <> acceptedDoc.tildaid WITH ph, tracksRel, scope, acceptedDoc, [m IN collect(matchDoc) WHERE m IS NOT NULL] as matchDocs WITH ph, tracksRel, scope, acceptedDoc, CASE WHEN ph.status <> ? THEN ? WHEN acceptedDoc IS NOT NULL THEN ? WHEN SIZE(matchDocs) > ? AND all(m IN matchDocs WHERE m.collection = ?) THEN ? WHEN SIZE(matchDocs) > ? THEN ? ELSE ? END as matchStatus RETURN CASE WHEN ph IS NULL THEN null ELSE {tildaid: ph.tildaid, fileName: ph.fileName, status: matchStatus, placeholderState: COALESCE(tracksRel.state, ?), archived: coalesce(ph.archived, false) OR scope.status = ?} END as deliverablePlaceholder } WITH collect(DISTINCT deliverablePlaceholder) as deliverablePlaceholders WITH [d IN deliverablePlaceholders WHERE d IS NOT NULL] as allPlaceholders RETURN allPlaceholders as deliverablePlaceholders, { total: size(allPlaceholders), pending: size([pp IN allPlaceholders WHERE pp.placeholderState IN [?, ?]]), pendingForDelete: size([pp IN allPlaceholders WHERE pp.placeholderState = ?]), accepted: size([pp IN allPlaceholders WHERE pp.placeholderState = ?]), rejected: size([pp IN allPlaceholders WHERE pp.placeholderState = ?]), incomplete: size([pp IN allPlaceholders WHERE pp.status = ?]), potentialMatchFound: size([pp IN allPlaceholders WHERE pp.status = ?]), matchExpected: size([pp IN allPlaceholders WHERE pp.status = ?]), matched: size([pp IN allPlaceholders WHERE pp.status = ?]), finalized: size([pp IN allPlaceholders WHERE pp.status = ?]), completion: {required: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]), completed: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]), aiSuggested: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), inStaging: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), noMatchFound: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), notConfirmed: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND pp.placeholderState = ? AND NOT pp.status = ?]), removed: size([pp IN allPlaceholders WHERE (pp.placeholderState = ? OR coalesce(pp.archived, false))]), percentComplete: CASE WHEN size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]) = ? THEN null ELSE toInteger(floor(?.? * size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]) / size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]))) END, isComplete: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]) > ? AND size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]) = size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)])} } as deliverableStats } // Aggregate all deliverables in the scope WITH scope, siteName, siteNumber, deliverableStats WITH distinct scope, siteName, siteNumber, { total: sum(deliverableStats.total), pending: sum(deliverableStats.pending), pendingForDelete: sum(deliverableStats.pendingForDelete), accepted: sum(deliverableStats.accepted), rejected: sum(deliverableStats.rejected), incomplete: sum(deliverableStats.incomplete), potentialMatchFound: sum(deliverableStats.potentialMatchFound), matchExpected: sum(deliverableStats.matchExpected), matched: sum(deliverableStats.matched), finalized: sum(deliverableStats.finalized), completion: {required: sum(deliverableStats.completion.required), completed: sum(deliverableStats.completion.completed), aiSuggested: sum(deliverableStats.completion.aiSuggested), inStaging: sum(deliverableStats.completion.inStaging), noMatchFound: sum(deliverableStats.completion.noMatchFound), notConfirmed: sum(deliverableStats.completion.notConfirmed), removed: sum(deliverableStats.completion.removed), percentComplete: CASE WHEN sum(deliverableStats.completion.required) = ? THEN null ELSE toInteger(floor(?.? * sum(deliverableStats.completion.completed) / sum(deliverableStats.completion.required))) END, isComplete: sum(deliverableStats.completion.required) > ? AND sum(deliverableStats.completion.completed) = sum(deliverableStats.completion.required)} } as scopeStats RETURN distinct scope{.*, siteName: siteName, siteNumber: siteNumber} as scope, scopeStats } WITH study, e, scope, scopeStats WHERE scope IS NOT NULL OR ($keepScopeless AND ($status = ? OR e.status = $status) AND NOT EXISTS { MATCH (e)<-[:PART_OF_EVENT]-(:EventScope) }) WITH study, e, scope.scopeId AS siteId, ? AS eventCount, coalesce(head(collect(scope.siteName)), ?) AS siteName, coalesce(head(collect(scope.siteNumber)), ?) AS siteNumber, {required: sum(scopeStats.completion.required), completed: sum(scopeStats.completion.completed), aiSuggested: sum(scopeStats.completion.aiSuggested), inStaging: sum(scopeStats.completion.inStaging), noMatchFound: sum(scopeStats.completion.noMatchFound), notConfirmed: sum(scopeStats.completion.notConfirmed), removed: sum(scopeStats.completion.removed), percentComplete: CASE WHEN sum(scopeStats.completion.required) = ? THEN null ELSE toInteger(floor(?.? * sum(scopeStats.completion.completed) / sum(scopeStats.completion.required))) END, isComplete: sum(scopeStats.completion.required) > ? AND sum(scopeStats.completion.completed) = sum(scopeStats.completion.required)} AS completion WITH {studyId: study.tildaid, studyName: coalesce(study.name, ?), completion: completion, eventId: e.tildaid, eventName: e.name, eventStatus: coalesce(e.status, ?), siteId: siteId, siteName: siteName, siteNumber: siteNumber, eventCount: eventCount} AS row WHERE ($minPercent IS NULL OR (row.completion.percentComplete IS NOT NULL AND row.completion.percentComplete >= $minPercent)) AND ($maxPercent IS NULL OR (row.completion.percentComplete IS NOT NULL AND row.completion.percentComplete <= $maxPercent)) AND ($isComplete IS NULL OR row.completion.isComplete = $isComplete) RETURN collect(row) AS rows, {required: sum(row.completion.required), completed: sum(row.completion.completed), aiSuggested: sum(row.completion.aiSuggested), inStaging: sum(row.completion.inStaging), noMatchFound: sum(row.completion.noMatchFound), notConfirmed: sum(row.completion.notConfirmed), removed: sum(row.completion.removed), percentComplete: CASE WHEN sum(row.completion.required) = ? THEN null ELSE toInteger(floor(?.? * sum(row.completion.completed) / sum(row.completion.required))) END, isComplete: sum(row.completion.required) > ? AND sum(row.completion.completed) = sum(row.completion.required)} AS completion
docsvc
2 708 ms 950 ms 975 ms flat
MATCH (e:Event {tildaid: $eventId, orgId: $orgId}) CALL { // Collect summary for each deliverable in the event WITH e OPTIONAL MATCH (e)-[:REQUIRES_RESOLUTION_OF]->(eventDel:Deliverable) RETURN CASE WHEN eventDel IS NOT NULL THEN eventDel{…
MATCH (e:Event {tildaid: $eventId, orgId: $orgId}) CALL { // Collect summary for each deliverable in the event WITH e OPTIONAL MATCH (e)-[:REQUIRES_RESOLUTION_OF]->(eventDel:Deliverable) RETURN CASE WHEN eventDel IS NOT NULL THEN eventDel{.*} ELSE null END as eventDeliverable } WITH e, collect(distinct eventDeliverable) as deliverables, [d IN collect(distinct eventDeliverable) WHERE d.warning IS NOT NULL AND d.warning <> ? | ??\? + d.warning] as deliverableWarnings OPTIONAL MATCH (e)<-[:EVENTS]-(ep:EventPlan)-[:PLAN_CONTEXT]->(study) WITH e, deliverables, deliverableWarnings, study CALL { // Get details for each scope in the event WITH e, study OPTIONAL MATCH (e)<-[:PART_OF_EVENT]-(scope:EventScope) OPTIONAL MATCH (site:Site {tildaid: scope.scopeId}) OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition{type:?})-[:PLAN_DEFINITION_TO_STUDY]->(study) WITH e, scope, CASE WHEN scope.scopeType = ? THEN site.name ELSE ? END as siteName, CASE WHEN scope.scopeType = ? THEN COALESCE(pd.siteNumber, ?) ELSE ? END as siteNumber OPTIONAL MATCH (scope)-[:APPLICABLE_TO]->(scopeDeliverable:Deliverable) CALL { // Get details of each deliverable applicable to the scope WITH scope, scopeDeliverable OPTIONAL MATCH (scopeDeliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document)-[:SCOPED_TO]->(scope) CALL { // Get match status for each placeholder WITH ph, tracksRel, scope OPTIONAL MATCH (ph)-[acceptedRel:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) OPTIONAL MATCH (ph)-[matchRel:MATCHING_DOCUMENT]->(matchDoc:Document) WHERE acceptedDoc IS NULL OR matchDoc.tildaid <> acceptedDoc.tildaid WITH ph, tracksRel, scope, acceptedDoc, [m IN collect(matchDoc) WHERE m IS NOT NULL] as matchDocs WITH ph, tracksRel, scope, acceptedDoc, CASE WHEN ph.status <> ? THEN ? WHEN acceptedDoc IS NOT NULL THEN ? WHEN SIZE(matchDocs) > ? AND all(m IN matchDocs WHERE m.collection = ?) THEN ? WHEN SIZE(matchDocs) > ? THEN ? ELSE ? END as matchStatus RETURN CASE WHEN ph IS NULL THEN null ELSE {tildaid: ph.tildaid, fileName: ph.fileName, status: matchStatus, placeholderState: COALESCE(tracksRel.state, ?), archived: coalesce(ph.archived, false) OR scope.status = ?} END as deliverablePlaceholder } WITH collect(DISTINCT deliverablePlaceholder) as deliverablePlaceholders WITH [d IN deliverablePlaceholders WHERE d IS NOT NULL] as allPlaceholders RETURN allPlaceholders as deliverablePlaceholders, { total: size(allPlaceholders), pending: size([pp IN allPlaceholders WHERE pp.placeholderState IN [?, ?]]), pendingForDelete: size([pp IN allPlaceholders WHERE pp.placeholderState = ?]), accepted: size([pp IN allPlaceholders WHERE pp.placeholderState = ?]), rejected: size([pp IN allPlaceholders WHERE pp.placeholderState = ?]), incomplete: size([pp IN allPlaceholders WHERE pp.status = ?]), potentialMatchFound: size([pp IN allPlaceholders WHERE pp.status = ?]), matchExpected: size([pp IN allPlaceholders WHERE pp.status = ?]), matched: size([pp IN allPlaceholders WHERE pp.status = ?]), finalized: size([pp IN allPlaceholders WHERE pp.status = ?]), completion: {required: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]), completed: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]), aiSuggested: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), inStaging: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), noMatchFound: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status = ?]), notConfirmed: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND pp.placeholderState = ? AND NOT pp.status = ?]), removed: size([pp IN allPlaceholders WHERE (pp.placeholderState = ? OR coalesce(pp.archived, false))]), percentComplete: CASE WHEN size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]) = ? THEN null ELSE toInteger(floor(?.? * size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]) / size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]))) END, isComplete: size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)]) > ? AND size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?) AND pp.status IN [?, ?]]) = size([pp IN allPlaceholders WHERE NOT (pp.placeholderState = ? OR coalesce(pp.archived, false)) AND (pp.placeholderState IN [?, ?] OR pp.status = ?)])} } as deliverableStats } // Aggregate all deliverables in the scope WITH e, scope, siteName, siteNumber, deliverableStats, CASE WHEN scopeDeliverable IS NOT NULL THEN scopeDeliverable{.*, trackedDocuments: [doc IN deliverablePlaceholders WHERE doc.tildaid IS NOT NULL], progress: deliverableStats } ELSE null END as deliverableDetails WITH distinct e, scope, siteName, siteNumber, collect(distinct deliverableDetails) as deliverablesInScope, { total: sum(deliverableStats.total), pending: sum(deliverableStats.pending), pendingForDelete: sum(deliverableStats.pendingForDelete), accepted: sum(deliverableStats.accepted), rejected: sum(deliverableStats.rejected), incomplete: sum(deliverableStats.incomplete), potentialMatchFound: sum(deliverableStats.potentialMatchFound), matchExpected: sum(deliverableStats.matchExpected), matched: sum(deliverableStats.matched), finalized: sum(deliverableStats.finalized), completion: {required: sum(deliverableStats.completion.required), completed: sum(deliverableStats.completion.completed), aiSuggested: sum(deliverableStats.completion.aiSuggested), inStaging: sum(deliverableStats.completion.inStaging), noMatchFound: sum(deliverableStats.completion.noMatchFound), notConfirmed: sum(deliverableStats.completion.notConfirmed), removed: sum(deliverableStats.completion.removed), percentComplete: CASE WHEN sum(deliverableStats.completion.required) = ? THEN null ELSE toInteger(floor(?.? * sum(deliverableStats.completion.completed) / sum(deliverableStats.completion.required))) END, isComplete: sum(deliverableStats.completion.required) > ? AND sum(deliverableStats.completion.completed) = sum(deliverableStats.completion.required)} } as scopeStats CALL { // Fetch rejected deliverables for this scope WITH scope OPTIONAL MATCH (scope)-[r:REJECTED_FROM]->(rejectedDel:Deliverable) RETURN CASE WHEN rejectedDel IS NOT NULL THEN { tildaid: rejectedDel.tildaid, name: rejectedDel.name, reason: r.reason, rejectedAt: r.createdat } ELSE null END as rejectedDeliverable } WITH distinct e, scope, siteName, siteNumber, deliverablesInScope, scopeStats, collect(distinct rejectedDeliverable) as rejectedDeliverables RETURN distinct scope{.*, deliverables: deliverablesInScope, progress: scopeStats, aiNotes: CASE WHEN scope.aiNotes IS NULL OR scope.aiNotes = ? THEN e.aiNotes ELSE scope.aiNotes END, decision: COALESCE(scope.decision, ?), rejectedDeliverables: [r IN rejectedDeliverables WHERE r IS NOT NULL], siteName: siteName, siteNumber: siteNumber} as scopeDetails, scopeStats } // Aggregate all scopes and all deliverables in the event WITH e, study, deliverables, deliverableWarnings, collect(scopeDetails) as eventScopes, { total: sum(scopeStats.total), pending: sum(scopeStats.pending), pendingForDelete: sum(scopeStats.pendingForDelete), accepted: sum(scopeStats.accepted), rejected: sum(scopeStats.rejected), incomplete: sum(scopeStats.incomplete), potentialMatchFound: sum(scopeStats.potentialMatchFound), matchExpected: sum(scopeStats.matchExpected), matched: sum(scopeStats.matched), finalized: sum(scopeStats.finalized), completion: {required: sum(scopeStats.completion.required), completed: sum(scopeStats.completion.completed), aiSuggested: sum(scopeStats.completion.aiSuggested), inStaging: sum(scopeStats.completion.inStaging), noMatchFound: sum(scopeStats.completion.noMatchFound), notConfirmed: sum(scopeStats.completion.notConfirmed), removed: sum(scopeStats.completion.removed), percentComplete: CASE WHEN sum(scopeStats.completion.required) = ? THEN null ELSE toInteger(floor(?.? * sum(scopeStats.completion.completed) / sum(scopeStats.completion.required))) END, isComplete: sum(scopeStats.completion.required) > ? AND sum(scopeStats.completion.completed) = sum(scopeStats.completion.required)} } as eventProgress WITH e, study, deliverables, deliverableWarnings, eventScopes, eventProgress, { totalCountries: size([s IN eventScopes WHERE s.scopeType = ?]), totalSites: size([s IN eventScopes WHERE s.scopeType = ?]), countries: [s IN eventScopes WHERE s.scopeType = ? | {name: s.scopeId, id: s.scopeId}], sites: [s IN eventScopes WHERE s.scopeType = ? | {name: s.siteName, id: s.scopeId, siteNumber: s.siteNumber}] } as scopeSummary CALL { // Fetch all ChangeLogs for the event WITH e // Audit items load through GET /changelogs; the event carries its newest ? rows at most. OPTIONAL MATCH (e)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE coalesce(cl.groupRole, ?) <> ? WITH cl ORDER BY cl.createdat DESC LIMIT ? WITH cl ORDER BY cl.createdat ASC RETURN collect(cl{.*}) as changeLogs } CALL { WITH e OPTIONAL MATCH (woc:WorkOrderConfig)-[:WORK_ORDER_CONFIG_TO_EVENT]->(e) RETURN woc.tildaid as workOrderConfigId LIMIT ? } RETURN e{.*, warnings: deliverableWarnings, deliverables: deliverables, scopeSummary: scopeSummary, eventScopes: eventScopes, progress: eventProgress, changeLogs: changeLogs, workOrderConfigId: workOrderConfigId, contextId: COALESCE(study.tildaid, ?), contextType: CASE WHEN study IS NULL THEN ? ELSE head(labels(study)) END, contextName: COALESCE(study.name, ?), aiState: CASE WHEN SIZE(eventScopes) = ? THEN ? WHEN SIZE([s IN eventScopes WHERE s.aiState <> ? AND s.aiState IS NOT NULL]) = ? THEN ? ELSE ? END} as result
docsvc
2 308 ms 950 ms 609 ms flat
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(a…
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) WITH ph, e, es, del, acceptedDoc, coalesce(acceptedDoc, ph) AS d OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) OPTIONAL MATCH (siteFromEvent: Site{tildaid: es.scopeId}) WHERE es.scopeType = ? OPTIONAL MATCH (d)-[:DOC_TO_ORG]->(o:Organization) OPTIONAL MATCH (d)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) where da.key in [?, ?, ?, ?] OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (uploader: User) OPTIONAL MATCH (study) - [:STUDY_TO_ACTOR] - (actor: Actor{userId: uploader.tildaid}) WITH d, e, es, del, b, study, b2, coalesce(site, siteFromEvent) AS site, o, head(collect(da)) as da, currentFolder, currentSubArtifact, stagingInfo, artifact, section, zone, collection, subArtifact, uploader, ph, acceptedDoc, head(collect(actor)) as actor CALL apoc.create.vNode([?], { tildaid: ph.tildaid, createdat: ph.createdat, updatedat: ph.updatedat, documentId: acceptedDoc.tildaid, documentCreatedAt: acceptedDoc.createdat, documentUpdatedAt: acceptedDoc.updatedat, orgName: o.name, orgId: o.tildaid, orgAlias: o.alias, studyName: study.name, studyId: study.tildaid, siteName: site.name, siteId: site.tildaid, documentStatus: d.status, country: CASE WHEN es.scopeType = ? THEN coalesce(es.scopeId, d.country) ELSE d.country END, languages: coalesce(apoc.convert.toJson(d.languages), ?), fileName: d.fileName, uploadedByName: uploader.name, uploadedById: uploader.tildaid, collection: d.collection, stateTag: d.stateTag, documentExpiryDate: da.value, zone: zone.name, zoneId: zone.tildaid, zoneNumber: case when zone.number is null then null else apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) end, section: section.name, sectionId: section.tildaid, sectionNumber: case when section.number is null then null else apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) end, artifact: artifact.name, artifactId: artifact.tildaid, artifactNumber: case when artifact.number is null then null else apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) end, subArtifact: subArtifact.name, subArtifactId: subArtifact.tildaid, subArtifactNumber: case when subArtifact.number is null then null else apoc.text.lpad(toString(COALESCE(subArtifact.number, ?)), ?, ?) end, eventId: e.tildaid, eventCreatedAt: e.createdat, eventType: e.type, eventName: e.name, eventStatus: e.status, eventScopeId: es.tildaid, eventScopeName: es.name, scopeType: es.scopeType, eventScopeDecision: es.decision, deliverableId: del.tildaid, deliverableName: del.name, deliverableLocationId: del.locationId, deliverableLocationType: del.locationType, deliverableType: del.type, roles: actor.roles }) YIELD node return node
write dashrsvc
3 300 ms 925 ms 894 ms flat
MATCH (study:Study {tildaid: $studyId})-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE NOT EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) CALL { WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]…
MATCH (study:Study {tildaid: $studyId})-[:MANAGED_BY]-(:Inbox)-[:BELONGS_TO]-(t:Task) WHERE NOT EXISTS ((t)-[:BELONGS_TO]-(:Inbox{type:?})) CALL { WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl)-[:PARTICIPANT_CONTROL]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw UNION ALL WITH t MATCH (t)<-[:CREATED_FROM_SPEC]-(ptc:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantActivity)-[:PARTICIPANT_ACTIVITY]-(pw:ParticipantWorkflow) RETURN ptc, pw } WITH DISTINCT study, t, ptc, pw MATCH (t)-[:BELONGS_TO]-(:Inbox)-[:MANAGED_BY]-(o:Organization) MATCH (ptc)-[:BELONGS_TO_TASKCONFIG]-(tc:TaskConfig) OPTIONAL MATCH (t)-[:BELONGS_TO]-(taskInbox:Inbox{type:?})--(taskUser:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:Actor{delpending:false})-[:FOR_USER]-(user:User) OPTIONAL MATCH (pw)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(scopeSite:Site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(o) WITH DISTINCT study, t, o, tc, taskInbox, taskUser, user, scopeSite SKIP $skip LIMIT $limit WITH collect({study:study, t:t, o:o, tc:tc, taskInbox:taskInbox, taskUser:taskUser, user:user, scopeSite:scopeSite}) AS rows, collect(DISTINCT study) AS studies, collect(DISTINCT o) AS orgs, collect(DISTINCT scopeSite) AS sites CALL { WITH studies UNWIND studies AS study OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(r:Role) WHERE r.name IN [?, ?, ?] RETURN study.tildaid AS studyId, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS piTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS smoTildaid, collect(DISTINCT CASE WHEN r.name = ? THEN u.tildaid END) AS raTildaid } WITH rows, orgs, sites, collect({studyId: studyId, pi: piTildaid, smo: smoTildaid, ra: raTildaid}) AS studyRoles CALL { WITH orgs UNWIND orgs AS o OPTIONAL MATCH (o)-[:ORGANIZATION_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN o.tildaid AS orgId, collect(DISTINCT u.tildaid) AS adminTildaid } WITH rows, sites, studyRoles, collect({orgId: orgId, admin: adminTildaid}) AS orgRoles CALL { WITH sites UNWIND (CASE WHEN size(sites) = ? THEN [null] ELSE sites END) AS scopeSite OPTIONAL MATCH (scopeSite)-[:STAFF_TO_ACTOR]-(a:Actor{delpending:false})-[:FOR_USER]-(u:User), (a)-[:APPOINTED_AS]-(:Role{name:?}) RETURN scopeSite.tildaid AS siteId, collect(DISTINCT u.tildaid) AS siteAdminTildaid } WITH rows, studyRoles, orgRoles, collect({siteId: siteId, admin: siteAdminTildaid}) AS siteRoles UNWIND rows AS row WITH row, [x IN studyRoles WHERE x.studyId = row.study.tildaid][?] AS sr, [x IN orgRoles WHERE x.orgId = row.o.tildaid][?] AS orr, [x IN siteRoles WHERE row.scopeSite IS NOT NULL AND x.siteId = row.scopeSite.tildaid][?] AS str RETURN DISTINCT row.t AS task, apoc.convert.toJson({userTildaid: row.user.tildaid, userName: row.user.name, PITildaid: sr.pi, studyOwnerTildaid: sr.smo, regAnalystTildaid: sr.ra, orgAdminTildaid: orr.admin, studyName: row.study.name, siteName: row.scopeSite.name, siteAdminTildaid: CASE WHEN row.scopeSite IS NULL THEN [] ELSE str.admin END}) AS details, row.tc.who AS dsnWho, row.o.alias AS orgAlias, row.taskInbox, row.taskUser.tildaid AS taskUserId
tusker
3 279 ms 925 ms 836 ms flat
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DE…
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DEFINITION_TO_SITE]->(s:Site) MATCH (a)-[:FOR_USER]->(u:User) WHERE a.state = $active AND u.status IN [$statusActive, $statusInvited] AND a.orgId = s.orgId RETURN DISTINCT u.email, s.name AS siteName, st.name AS studyName
tusker
3 251 ms 925 ms 709 ms flat
MATCH (root:GraphNode{tildaid:$graphId}) OPTIONAL MATCH (root)-[:GRAPH_TO_VARIABLE{graphid:root.tildaid}]-(var:VariableV2) WITH root, collect(DISTINCT var) as graphVars MATCH (root)-[rr:GRAPH_NEXT*?..{graphid:root.tildaid}]->(gnode:GraphNo…
MATCH (root:GraphNode{tildaid:$graphId}) OPTIONAL MATCH (root)-[:GRAPH_TO_VARIABLE{graphid:root.tildaid}]-(var:VariableV2) WITH root, collect(DISTINCT var) as graphVars MATCH (root)-[rr:GRAPH_NEXT*?..{graphid:root.tildaid}]->(gnode:GraphNode) WHERE size(rr) = ? OR rr[-?].path[?] = root.tildaid OPTIONAL MATCH (gnode)-[:GRAPH_NODE_TO_PAGE{graphid:root.tildaid}]->(page:GraphPage) OPTIONAL MATCH (gnode)-[:GRAPH_NODE_REF_TO_GRAPH_NODE{protocolid:$relPropId}]->(refNode:GraphNode) WHERE gnode.objtype = ? OPTIONAL MATCH (gnode)-[:GRAPH_NODE_REF_TO_QUESTION{protocolid:$relPropId}]->(question:QuestionV2) WHERE refNode.objtype = ? WITH root, gnode, page, refNode, case when refNode.objtype = ? then question when refNode.objtype = ? then refNode else null end as question, rr, graphVars OPTIONAL MATCH (question)-[:QUESTION_TO_QUESTION_SPEC]->(questionSpec:QuestionSpec) OPTIONAL MATCH (gnode)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{protocolid:$relPropId}]- (nodeBranchCfg:BranchConfig)-[:BRANCH_TO_BRANCH_CONFIG{protocolid:$relPropId}]- (nodeBranch:BranchV2) OPTIONAL MATCH (gnode)-[cfgRel:BRANCH_CFG_TO_GRAPH_NODE_REF{protocolid:$relPropId}]- (:BranchConfig)-[:ACTIVATION_RULE_TO_BRANCH_CONFIG{graphid:cfgRel.graphid}]- (:ActivationRule)-[:ACTIVATION_RULE_TO_CHAIN{graphid:cfgRel.graphid}]- (nodeChain:ActivationRuleChain) OPTIONAL MATCH (gnode)-[:BRANCH_TO_BRANCH_CONFIG{graphid:root.tildaid, protocolid:$relPropId}]->(cfg:BranchConfig) WHERE gnode.objtype = ? OPTIONAL MATCH (cfg)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{protocolid:$relPropId}]->(cfgRef:GraphNodeRef) OPTIONAL MATCH (gnode)-[rel:QUESTION_TO_AUTO_COMPUTE_CFG|QUESTION_TO_CORRECTNESS_CHECK{protocolid:$relPropId}]->(acc:AutoComputeConfig) OPTIONAL MATCH (acc)-[:AUTO_COMPUTE_TO_BRANCH_CONFIG]->(acBranchCfg:BranchConfig) OPTIONAL MATCH (acBranchCfg)-[:BRANCH_CFG_TO_GRAPH_NODE_REF{protocolid:$relPropId}]->(acCfgRef:GraphNodeRef) OPTIONAL MATCH (gnode)-[:QUESTION_TO_AUTO_QUERY{protocolid:$relPropId}]->(aqcfg:AutoQueryConfig{enabled:true}) OPTIONAL MATCH (aqcfg)-[:AUTO_QUERY_TO_BRANCH_CONFIG]->(aqBranchCfg:BranchConfig) WITH gnode, page, refNode, cfg, rr, questionSpec, question, CASE WHEN size(rr) > ? THEN rr[-?].path ELSE [root.tildaid] END as nodePath, CASE WHEN size(rr) > ? THEN rr[-?].flags ELSE ? END as nodeFlags, CASE WHEN size(rr) > ? THEN rr[-?].who ELSE [] END as nodeWho, CASE WHEN type(rel) = ? THEN true ELSE false END as isCorrectnessCheckConfig, collect(DISTINCT cfgRef.tildaid) as cfgRefIds, collect(DISTINCT nodeBranch.tildaid) as branchIds, collect(DISTINCT nodeChain.tildaid) as activationChainIds, acc, acBranchCfg, collect(DISTINCT acCfgRef.tildaid) as autoComputeBranchRefIds, graphVars, collect(DISTINCT CASE WHEN aqcfg IS NOT NULL THEN {aqcfg: aqcfg, aqBranchCfg: aqBranchCfg} END) as autoQueryConfigs WITH gnode as node, page, refNode, cfg as branchCfg, cfgRefIds as branchRefIds, questionSpec, question, branchIds, activationChainIds, nodePath as path, gnode.objtype as objType, nodeFlags as flags, nodeWho as who, acc as autoComputeConfig, acBranchCfg as autoComputeBranchCfg, autoComputeBranchRefIds, autoQueryConfigs, isCorrectnessCheckConfig, graphVars RETURN {node:node, page:page, refNode:refNode, questionSpec:questionSpec, question:question, branchCfg:branchCfg, branchRefIds:branchRefIds, path:path, objType:objType, flags:flags, who:who, branchIds:branchIds, activationChainIds:activationChainIds, autoComputeConfig:autoComputeConfig, autoComputeBranchCfg:autoComputeBranchCfg, autoComputeBranchRefIds:autoComputeBranchRefIds, autoQueryConfigs:autoQueryConfigs, isCorrectnessCheckConfig:isCorrectnessCheckConfig, graphVars:graphVars}
execsvc
6 263 ms 925 ms 880 ms flat
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {de…
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)<-[:FOR_USER]-(da:Actor {delpending: false})<-[:STUDY_TO_DELEGATED_ACTOR]-(dst:Study {delpending: false})-[:STUDY_TO_ORG]->(o:Organization {delpending: false}), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor {delpending: false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias: $tildaScaleAlias}) WHERE dst.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) RETURN o UNION WITH u MATCH (o:Organization {alias: $activeAlias, delpending: false}) RETURN o } OPTIONAL MATCH (st:Study {delpending: false})-[:STUDY_TO_ORG]->(o) RETURN collect(DISTINCT o.alias) AS aliases, collect(DISTINCT o.tildaid) AS orgIds, collect(DISTINCT st.tildaid) AS studyIds
dashql
6 186 ms 925 ms 543 ms flat
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {de…
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)<-[:FOR_USER]-(da:Actor {delpending: false})<-[:STUDY_TO_DELEGATED_ACTOR]-(dst:Study {delpending: false})-[:STUDY_TO_ORG]->(o:Organization {delpending: false}), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor {delpending: false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias: $tildaScaleAlias}) WHERE dst.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) RETURN o UNION WITH u MATCH (o:Organization {alias: $activeAlias, delpending: false}) RETURN o } OPTIONAL MATCH (st:Study {delpending: false})-[:STUDY_TO_ORG]->(o) RETURN collect(DISTINCT o.alias) AS aliases, collect(DISTINCT o.tildaid) AS orgIds, collect(DISTINCT st.tildaid) AS studyIds
dashql
3 288 ms 925 ms 850 ms flat
match (org:Organization{tildaid: $orgId})-[:STUDY_TO_ORG]-(st:Study{tildaid: $studyId}) call { with org,st with org,st match (org)-[:STUDY_TO_ORG]-(st)-[:PLAN_DEFINITION_TO_STUDY]- (pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Si…
match (org:Organization{tildaid: $orgId})-[:STUDY_TO_ORG]-(st:Study{tildaid: $studyId}) call { with org,st with org,st match (org)-[:STUDY_TO_ORG]-(st)-[:PLAN_DEFINITION_TO_STUDY]- (pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(siteOrg:Organization) where (org.orgType=? or org.orgType=?) and pd.delpending=false AND site.tildaid in $siteIds return site, pd, siteOrg.tildaid as siteOrgID, siteOrg.name as siteOrgName union with org,st with org,st match (org)-[:IS_AN_ORG]-(:Smo)-[:SITE_TO_SMO]-(site:Site)-[:PLAN_DEFINITION_TO_SITE]- (pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_STUDY]-(st) where pd.delpending=false AND site.tildaid in $siteIds return site, pd, org.tildaid as siteOrgID, org.name as siteOrgName } optional match (pd)<-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(ssa:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_ACTOR]-(a:Actor)-[:FOR_USER]-(u:User) where a.state<>$actorState // Add optional budget match that requires both site and study match optional match (b:Budget) where (b)-[:BUDGET_TO_SITE]->(site) and (b)-[:BUDGET_TO_STUDY]->(st) // Add optional protocol match that requires both site and study match optional match (protocol:ProtocolVersionV2)-[protocolRel:PLAN_DEFINITION_TO_PUBLISHED_PROTOCOL_VERSIONS|PLAN_DEFINITION_TO_PARENT_PUBLISHED_PROTOCOL_VERSIONS|PLAN_DEFINITION_TO_ADOPTED_PROTOCOL_VERSIONS]-(pd) with (case when a is not null and a.orgId = site.orgId then ? else ? end) as teamCount, a, u, ssa, site, pd, b, collect(distinct {protocol:protocol,protocolRel:type(protocolRel)}) as protocolList, siteOrgID, siteOrgName call{ with a, u, ssa, site // Site staff only, the same discriminator teamCount above applies: an // org-level study actor holds an SSA at every study site while carrying // their own org?s own ClinOp proxy name/email (scoped by orgId), // falling back to the canonical User. For SMO callers no ClinOp matches, // so they get the canonical values. optional match (co:ClinOp{deltimestamp:?, orgId:$orgId})-[:FOR_USER]-(u) return collect({ name: case when coalesce(co.actorName,?)<>? then co.actorName else coalesce(u.name, u.email) end, email: case when coalesce(co.actorEmail,?)<>? then co.actorEmail else u.email end }) as piList } call{ with a, u, ssa, site // ENG-?: same site-staff discriminator and identity as piList, for the requested roles. with a, u, ssa where size($roles) > ? and ANY(role IN ssa.roles WHERE role IN $roles) and a.orgId = site.orgId optional match (rco:ClinOp{deltimestamp:?, orgId:$orgId})-[:FOR_USER]-(u) return collect({ name: case when coalesce(rco.actorName,?)<>? then rco.actorName else coalesce(u.name, u.email) end, email: case when coalesce(rco.actorEmail,?)<>? then rco.actorEmail else u.email end, roles: [role IN ssa.roles WHERE role IN $roles] }) as roleList } call{ with pd optional match (pd)-[:SOA_TO_PLAN_DEFINITION]-(soa) return soa } call{ with pd // Contacts are scoped to the calling org?s // seedKey: the stable routing category the three buckets below match on. // A contact on any other area (or a custom one, which has no seedKey) // lands in no bucket, exactly as it did when t was the lowercased name. optional match (copdca:ContactArea{tildaid: copdc.contactAreaId}) return [row in collect(case when copdc is not null and co is not null and cou is not null and cou.status <> $deletedStatus and cou.email is not null and cou.email <> ? then {t: coalesce(copdca.seedKey, ?), p: coalesce(copdc.isPrimary, false), e: case when coalesce(co.actorEmail,?)<>? then co.actorEmail else cou.email end} end) where row is not null] as pdContactRows } call{ with site optional match (site)-[:SITE_TO_SMO]-(:Smo)-[:IS_AN_ORG]-(ownerOrg:Organization) return ($managedStatus in collect(ownerOrg.status)) as managed } with site as site, sum(teamCount) as teamCount, [x in collect(piList) where size(x)>?] as piListAll, [x in collect(roleList) where size(x)>?] as roleListAll, soa as soa, pd.status as pdStatus, pd.siteNumber as siteNumber, b as budget, protocolList as protocolList, siteOrgID as siteOrgID, siteOrgName as siteOrgName, pdContactRows as pdContactRows, managed as managed return {site:site,teamCount:teamCount,piList:piListAll,roleList:roleListAll,soa:soa,pdStatus:pdStatus,siteNumber:siteNumber,budget:budget,protocolList:protocolList,siteOrgID:siteOrgID,siteOrgName:siteOrgName,pdContactRows:pdContactRows,managed:managed}
execsvc
4 192 ms 900 ms 689 ms flat
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=Organization policies=org-resources MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANI…
MATCH (u:User {tildaid:$user}) OPTIONAL MATCH (o:Organization {alias:$org}) UNWIND $lists.checks AS c CALL { WITH u, o, c // scope=Organization policies=org-resources MATCH (u)<-[:FOR_USER]-(a:Actor)-[:APPOINTED_AS]->(x:Role), (o)-[:ORGANIZATION_TO_ACTOR]->(a) WHERE a.state <> ? AND a.delpending = false AND x.name IN $lists.roles_0 MATCH (o)-[:ORGANIZATION_TO_PATIENT]->(p2:Patient {tildaid:c.id}) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient UNION WITH u, o, c // scope=Organization policies=org-resources MATCH (u)<-[:FOR_USER]-(da:Actor)<-[:STUDY_TO_DELEGATED_ACTOR]-(st:Study)-[:STUDY_TO_ORG]->(o), (da)-[:APPOINTED_AS]->(x:Role)<-[:APPOINTED_AS]-(sa:Actor), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias:$tildaScaleAlias}) WHERE st.srcorgid = o.tildaid AND da.state <> ? AND da.delpending = false AND sa.state <> ? AND sa.delpending = false AND x.name IN $lists.roles_1 AND ? IN apoc.text.split(coalesce(x.tags, ?), ?) MATCH (o)-[:ORGANIZATION_TO_PATIENT]->(p2:Patient {tildaid:c.id}) RETURN DISTINCT c.idx AS checkIdx, u AS User, o AS Organization, null AS Study, null AS Site, null AS Patient } RETURN checkIdx, User, Organization, Study, Site, Patient
patientsvc
4 229 ms 900 ms 887 ms flat
MATCH (d:Document) -[:DOCUMENT_VERSION] - (dv: DocumentVersion {tildaid: $id}) OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) OPTIONAL MATCH (d…
MATCH (d:Document) -[:DOCUMENT_VERSION] - (dv: DocumentVersion {tildaid: $id}) OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) OPTIONAL MATCH (d)-[:DOC_TO_ORG]->(o:Organization) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (uploader: User) OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) CALL { WITH dv OPTIONAL MATCH (dv)<-[:DOCUMENT_SOURCE_TO_VERSION]-(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(da: DocAttribute) WITH collect([coalesce(da.key, ?), coalesce(da.value, ?)]) AS attributePairs WITH apoc.map.fromPairs(attributePairs) AS attributesMap WITH apoc.convert.toJson(attributesMap) AS attributesJson RETURN attributesJson } WITH d, dv, o, study, site, zone, section, artifact, subArtifact, uploader, attributesJson CALL apoc.create.vNode([?], { documentId: d.tildaid, orgId: o.tildaid, orgName: o.name, orgAlias: o.alias, studyId: study.tildaid, studyName: study.name, siteId: COALESCE(site.tildaid, ?), siteName: COALESCE(site.name, ?), zone: zone.name, zoneId: zone.tildaid, section: section.name, sectionId: section.tildaid, artifact: artifact.name, artifactId: artifact.tildaid, subArtifact: subArtifact.name, subArtifactId: subArtifact.tildaid, documentMetadata: attributesJson, uploadedByName: uploader.name, uploadedById: uploader.tildaid, createdat: d.createdat, updatedat: dv.updatedat, artifactNumber: apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?), subArtifactNumber: case when subArtifact.number is null then null else apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(subArtifact.number, ?)), ?, ?) end, tmfLevel: d.tmfLevel, country: d.country, language: d.language, fileName: d.fileName } ) YIELD node RETURN node
write dashrsvc
5 183 ms 875 ms 901 ms flat
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {de…
OPTIONAL MATCH (u:User {tildaid: $userId}) CALL { WITH u MATCH (u)<-[:FOR_USER]-(:Actor {delpending: false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)-[:SUPPORTS_ORG]-(o:Organization {delpending: false}) RETURN o UNION WITH u MATCH (u)<-[:FOR_USER]-(da:Actor {delpending: false})<-[:STUDY_TO_DELEGATED_ACTOR]-(dst:Study {delpending: false})-[:STUDY_TO_ORG]->(o:Organization {delpending: false}), (da)-[:APPOINTED_AS]->(dr:Role)<-[:APPOINTED_AS]-(sa:Actor {delpending: false}), (da)-[:DELEGATED_FROM]->(sa)<-[:ORGANIZATION_TO_ACTOR]-(:Organization {alias: $tildaScaleAlias}) WHERE dst.srcorgid = o.tildaid AND da.state <> ? AND sa.state <> ? AND $orgDelegatedTag IN apoc.text.split(coalesce(dr.tags, ?), ?) RETURN o UNION WITH u MATCH (o:Organization {alias: $activeAlias, delpending: false}) RETURN o } OPTIONAL MATCH (st:Study {delpending: false})-[:STUDY_TO_ORG]->(o) RETURN collect(DISTINCT o.alias) AS aliases, collect(DISTINCT o.tildaid) AS orgIds, collect(DISTINCT st.tildaid) AS studyIds
dashql
5 179 ms 875 ms 871 ms 0.0× ↓
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- …
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]- (pd:DslPlanDefinition) MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)-[:SITE_TO_SMO]- (smo)-[:IS_AN_ORG]-(org:Organization) OPTIONAL MATCH (visit:VisitV2)-[:BELONGS_TO_VISIT]-(pv) WITH pv, cp, st, site, org, visit OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(emptyPa:ParticipantActivity) WHERE (emptyPa.edcStatus IS NULL OR emptyPa.edcStatus = ?) AND emptyPa.deltimestamp = ? WITH pv, cp, st, site, org, visit, collect(emptyPa) AS emptyPas OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity)-[:BELONGS_TO_ACTIVITY]->(act:ActivityV2) WHERE pa.deltimestamp = ? RETURN pv, cp, st, site, org, visit, emptyPas, collect({pa: pa, activity: act}) AS paActPairs LIMIT ?
execsvc
6 402 ms 850 ms 518 ms flat
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DE…
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DEFINITION_TO_SITE]->(s:Site) MATCH (a)-[:FOR_USER]->(u:User) WHERE a.state = $active AND u.status IN [$statusActive, $statusInvited] AND a.orgId = s.orgId RETURN DISTINCT u.email, s.name AS siteName, st.name AS studyName
tusker
18 147 ms 850 ms 820 ms flat
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DE…
MATCH (org:Organization {alias: $alias, deltimestamp: ?}) <-[:STUDY_TO_ORG]-(st:Study) -[:STUDY_TO_ACTOR]->(a:Actor) <-[:STUDYSITEACTOR_TO_ACTOR]-(ssa:StudySiteActor) -[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(pd:DslPlanDefinition) -[:PLAN_DEFINITION_TO_SITE]->(s:Site) MATCH (a)-[:FOR_USER]->(u:User) WHERE a.state = $active AND u.status IN [$statusActive, $statusInvited] AND a.orgId = s.orgId RETURN DISTINCT u.email, s.name AS siteName, st.name AS studyName
tusker
6 152 ms 850 ms 824 ms flat
match (pv:ParticipantVisit)-[:BELONGS_TO_PARTICIPANT_VISIT]-(v:VisitAppointment)-[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(c:CarePlan)-[:RESEARCH_SUBJECT] -(p:Patient)-[:HAS_PROTECTED_DATA_IN]-(phi:Phi) where c.status IN [?,?] and v.startTime >= …
match (pv:ParticipantVisit)-[:BELONGS_TO_PARTICIPANT_VISIT]-(v:VisitAppointment)-[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(c:CarePlan)-[:RESEARCH_SUBJECT] -(p:Patient)-[:HAS_PROTECTED_DATA_IN]-(phi:Phi) where c.status IN [?,?] and v.startTime >= datetime({epochMillis:$day1Start}) and v.startTime <= datetime({epochMillis:$day1End}) and coalesce(v.reminderStatus, ?) = ? match (c)-[:EXECUTE_SOA]-(s:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION] -(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(si:Site)-[:SITE_TO_SMO]->(smo:Smo)-[:IS_AN_ORG]->(o:Organization) return v,phi,si,p,o
schedsvc
6 151 ms 850 ms 785 ms flat
// First part for StudySiteUser scope match (:StudySiteDuty)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]->(scopeUser:StudySiteActor{delpending:false}) with distinct scopeUser match (scopeUser)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(sitePd:DslPlanDe…
// First part for StudySiteUser scope match (:StudySiteDuty)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]->(scopeUser:StudySiteActor{delpending:false}) with distinct scopeUser match (scopeUser)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(sitePd:DslPlanDefinition{type:?}) match (sitePd)-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]->(w:WorkflowV2)<-[:TRIGGER_TO_WORKFLOW]-(t:Trigger{scope:?}) match (sitePd)-[:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ORG]->(o:Organization{orgType:?}) match (sitePd)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) match (scopeUser)-[:STUDYSITEACTOR_TO_ACTOR]->(actor:Actor{delpending:false})<-[:STUDY_TO_ACTOR]-(st) where not exists((scopeUser)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:ParticipantWorkflow)) return distinct scopeUser.tildaid as scopeTildaid, t.scope as workflowScope, st.tildaid as stId, o.alias as orgAlias, site.tildaid as siteId, scopeUser.roles as roles UNION // Second part for StudySite scope match (t:Trigger{scope:?})-[:TRIGGER_TO_WORKFLOW]->(w:WorkflowV2) match (w)<-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]-(scopeSite:DslPlanDefinition{type:?}) where not exists((scopeSite)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:ParticipantWorkflow)) with distinct t.scope as workflowScope, scopeSite match (scopeSite)-[:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ORG]->(o:Organization{orgType:?}) match (scopeSite)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) return distinct scopeSite.tildaid as scopeTildaid, workflowScope, st.tildaid as stId, o.alias as orgAlias, site.tildaid as siteId, null as roles
write dashql
61 126 ms 795 ms 3.06 s 0.0× ↓
// First part for StudySiteUser scope match (:StudySiteDuty)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]->(scopeUser:StudySiteActor{delpending:false}) with distinct scopeUser match (scopeUser)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(sitePd:DslPlanDe…
// First part for StudySiteUser scope match (:StudySiteDuty)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]->(scopeUser:StudySiteActor{delpending:false}) with distinct scopeUser match (scopeUser)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]->(sitePd:DslPlanDefinition{type:?}) match (sitePd)-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]->(w:WorkflowV2)<-[:TRIGGER_TO_WORKFLOW]-(t:Trigger{scope:?}) match (sitePd)-[:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ORG]->(o:Organization{orgType:?}) match (sitePd)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) match (scopeUser)-[:STUDYSITEACTOR_TO_ACTOR]->(actor:Actor{delpending:false})<-[:STUDY_TO_ACTOR]-(st) where not exists((scopeUser)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:ParticipantWorkflow)) return distinct scopeUser.tildaid as scopeTildaid, t.scope as workflowScope, st.tildaid as stId, o.alias as orgAlias, site.tildaid as siteId, scopeUser.roles as roles UNION // Second part for StudySite scope match (t:Trigger{scope:?})-[:TRIGGER_TO_WORKFLOW]->(w:WorkflowV2) match (w)<-[:PLAN_DEFINITION_TO_ADOPTED_WORKFLOWS]-(scopeSite:DslPlanDefinition{type:?}) where not exists((scopeSite)-[:PARTICIPANTWORKFLOW_TO_SCOPE]-(:ParticipantWorkflow)) with distinct t.scope as workflowScope, scopeSite match (scopeSite)-[:PLAN_DEFINITION_TO_STUDY]->(st:Study)-[:STUDY_TO_ORG]->(o:Organization{orgType:?}) match (scopeSite)-[:PLAN_DEFINITION_TO_SITE]->(site:Site) return distinct scopeSite.tildaid as scopeTildaid, workflowScope, st.tildaid as stId, o.alias as orgAlias, site.tildaid as siteId, null as roles
regsvc
9 78 ms 775 ms 643 ms flat
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor…
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor WITH u, u{.*, pinSet: pinSet, needs2Factor: needs2Factor} as profile CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, a, r order by r.displayName WITH u, o, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, orgType: o.orgType, status: o.status, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, role: roles, displayRoles: displayRoles, logoDocumentId: o.logoDocumentId, theme: o.theme, logoURL: o.logoURL, pinCheckEnabled: o.pinCheckEnabled }) AS organizations } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false,orgId:$orgId})<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false}) WITH DISTINCT s, a, u.capabilities AS caps CALL { WITH a OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH r order by r.displayName RETURN apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles } MATCH (s)-[:STUDY_TO_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) // DISTINCT restores the dedup the old (u,o,s,a) grouping gave for free: two parallel // STUDY_TO_ORG edges would otherwise emit the study twice. The ORDER BY makes the // studies array a stable sequence for clients rather than a plan-dependent one. WITH DISTINCT s, a, caps, roles, displayRoles, o ORDER BY s.name, s.tildaid, o.tildaid RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, studyName: s.name, studySponsor: s.sponsor, studyId: s.tildaid, startDate: a.startDate, endDate: a.endDate, capabilities: caps, iSBlinded: a.isBlinded, role: roles, displayRoles: displayRoles }) AS studies } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, siteId: si.tildaid, siteName: si.name, siteCity: si.city, contactPhone: si.contactPhone, contactEmail: si.contactEmail, defaultLanguage: si.defaultLanguage, role: roles, displayRoles: displayRoles }) as sites } return profile{.*, organizations: organizations, studies: studies, sites: sites}
tusker
12 89 ms 700 ms 561 ms flat
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(a…
MATCH (ph: Document {tildaid: $id}) MATCH (e: Event) - [:PART_OF_EVENT] - (es: EventScope) - [:APPLICABLE_TO] - (del: Deliverable) - [:TRACKS_PLACEHOLDER] - (ph) MATCH (ph) - [:SCOPED_TO] - (es) OPTIONAL MATCH (ph)-[:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) WITH ph, e, es, del, acceptedDoc, coalesce(acceptedDoc, ph) AS d OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) OPTIONAL MATCH (siteFromEvent: Site{tildaid: es.scopeId}) WHERE es.scopeType = ? OPTIONAL MATCH (d)-[:DOC_TO_ORG]->(o:Organization) OPTIONAL MATCH (d)-[:DOCUMENT_TO_SOURCE]->(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) where da.key in [?, ?, ?, ?] OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (uploader: User) OPTIONAL MATCH (study) - [:STUDY_TO_ACTOR] - (actor: Actor{userId: uploader.tildaid}) WITH d, e, es, del, b, study, b2, coalesce(site, siteFromEvent) AS site, o, head(collect(da)) as da, currentFolder, currentSubArtifact, stagingInfo, artifact, section, zone, collection, subArtifact, uploader, ph, acceptedDoc, head(collect(actor)) as actor CALL apoc.create.vNode([?], { tildaid: ph.tildaid, createdat: ph.createdat, updatedat: ph.updatedat, documentId: acceptedDoc.tildaid, documentCreatedAt: acceptedDoc.createdat, documentUpdatedAt: acceptedDoc.updatedat, orgName: o.name, orgId: o.tildaid, orgAlias: o.alias, studyName: study.name, studyId: study.tildaid, siteName: site.name, siteId: site.tildaid, documentStatus: d.status, country: CASE WHEN es.scopeType = ? THEN coalesce(es.scopeId, d.country) ELSE d.country END, languages: coalesce(apoc.convert.toJson(d.languages), ?), fileName: d.fileName, uploadedByName: uploader.name, uploadedById: uploader.tildaid, collection: d.collection, stateTag: d.stateTag, documentExpiryDate: da.value, zone: zone.name, zoneId: zone.tildaid, zoneNumber: case when zone.number is null then null else apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) end, section: section.name, sectionId: section.tildaid, sectionNumber: case when section.number is null then null else apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) end, artifact: artifact.name, artifactId: artifact.tildaid, artifactNumber: case when artifact.number is null then null else apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) end, subArtifact: subArtifact.name, subArtifactId: subArtifact.tildaid, subArtifactNumber: case when subArtifact.number is null then null else apoc.text.lpad(toString(COALESCE(subArtifact.number, ?)), ?, ?) end, eventId: e.tildaid, eventCreatedAt: e.createdat, eventType: e.type, eventName: e.name, eventStatus: e.status, eventScopeId: es.tildaid, eventScopeName: es.name, scopeType: es.scopeType, eventScopeDecision: es.decision, deliverableId: del.tildaid, deliverableName: del.name, deliverableLocationId: del.locationId, deliverableLocationType: del.locationType, deliverableType: del.type, roles: actor.roles }) YIELD node return node
write dashrsvc
110 61 ms 656 ms 870 ms flat
MATCH (d:Document {tildaid: $id}) MATCH (r:Recommendation)-[:RECOMMENDATION_TO_DOCUMENT]->(d) WHERE NOT r.category IN [?, ?, ?] MATCH (ai:AIRun)-[:AI_RUN_TO_RECOMMENDATION]->(r) MATCH (r)-[:RECOMMENDATION_TO_ORGANIZATION]->(o:Organization)…
MATCH (d:Document {tildaid: $id}) MATCH (r:Recommendation)-[:RECOMMENDATION_TO_DOCUMENT]->(d) WHERE NOT r.category IN [?, ?, ?] MATCH (ai:AIRun)-[:AI_RUN_TO_RECOMMENDATION]->(r) MATCH (r)-[:RECOMMENDATION_TO_ORGANIZATION]->(o:Organization) OPTIONAL MATCH (d) - [:UPLOADED_BY] - (u: User) OPTIONAL MATCH (d)<-[:INCLUDES]-(currentFolder:Folder{folderType:?, delpending: FALSE}) WHERE currentFolder.scope IN [?, ?] OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(currentSubArtifact:SubArtifact)<-[:INCLUDES]-(currentFolder) OPTIONAL MATCH (d)-[:HAS_STAGING_INFO]->(stagingInfo:DocumentStagingInfo) OPTIONAL MATCH (artifact:Folder{tildaid:coalesce(stagingInfo.folderId, currentFolder.tildaid, ?)})<-[:INCLUDES]-(section:Folder)<-[:INCLUDES]-(zone:Folder)<-[:INCLUDES]-(collection:Folder{level:?}) OPTIONAL MATCH (artifact)-[:INCLUDES]->(subArtifact:SubArtifact{tildaid:coalesce(stagingInfo.subArtifactId, currentSubArtifact.tildaid, ?)}) OPTIONAL MATCH (dv:DocumentVersion{tildaid: d.primaryVersionId}) OPTIONAL MATCH (d)<-[:INCLUDES]-(b:Binder)-[:BINDER_FOR]->(study:Study) OPTIONAL MATCH (d)<-[:INCLUDES]-(b2:Binder)-[:BINDER_FOR]->(site:Site) CALL { WITH d, site OPTIONAL MATCH (study:Study{tildaid: d.studyId})<-[:PLAN_DEFINITION_TO_STUDY]- (pd:DslPlanDefinition{delpending:false, type:?})-[:PLAN_DEFINITION_TO_SITE]->(site) RETURN COALESCE(pd.siteNumber, ?) AS siteNumber } CALL { WITH dv OPTIONAL MATCH (dv)<-[:DOCUMENT_SOURCE_TO_VERSION]-(ds:DocumentSource)-[:HAS_ATTRIBUTE]->(docAttr: DocAttribute) WITH collect([coalesce(docAttr.key, ?), coalesce(docAttr.value, ?)]) AS attributePairs WITH apoc.map.fromPairs(attributePairs) AS attributesMap WITH apoc.convert.toJson(attributesMap) AS attributesJson RETURN attributesJson } // Latest state-change ChangeLog on the version — gives us the user // who triggered the doc into its current state. Merge both event // types (Staging + StateTag) since the BE treats them as the same // documentState concept (mirrors the Python migration script). CALL { WITH dv OPTIONAL MATCH (dv)-[:ALL_CHANGELOG]-(dvcl:ChangeLog) WHERE dvcl.type IN [?, ?, ?] AND (dvcl.newValue CONTAINS ? OR dvcl.newValue CONTAINS ?) WITH dvcl ORDER BY dvcl.createdat DESC LIMIT ? OPTIONAL MATCH (changer:User {tildaid: dvcl.changedBy}) WHERE dvcl IS NOT NULL RETURN coalesce(dvcl.changedBy, ?) as statePromotedBy, coalesce(changer.name, ?) as statePromotedByName } WITH r, ai, o, d, dv, zone, section, artifact, subArtifact, u, study, site, siteNumber, attributesJson, statePromotedBy, statePromotedByName, // Document Scope key stripping: reduce the rec JSON envelope to the keys // valid for its OWN scope so irrelevant keys (e.g. country on a site rec) // don?scope?country?scope?siteName?siteId?siteNumber?scope?scope???{}?{}?????{}?{?{?{????DRecommendation????s ORIGINAL prediction (orig_data). // Same Document Scope extraction so this column lines up with // recommendedValue / actualValue. originalRecommendation: coalesce(r.orig_data, ?), recommendationCount: CASE r.category WHEN ? THEN size(keys(apoc.map.clean( apoc.convert.fromJsonMap(coalesce(r.data_formatted, ?)), [], [?]))) WHEN ? THEN apoc.coll.max([?, size([k IN keys(recMap) WHERE (recAllowed IS NULL OR k IN recAllowed) AND coalesce(toString(recMap[k]), ?) <> ?])]) ELSE ? END, actualValue: CASE r.category WHEN ? THEN coalesce(d.fileName, ?) WHEN ? THEN apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) + ? + coalesce(artifact.name, ?) WHEN ? THEN coalesce(subArtifact.name, ?) WHEN ? THEN apoc.convert.toJson( CASE toLower(coalesce(d.tmfLevel, ?)) WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), country: coalesce(d.country, ?)} WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), siteId: coalesce(site.tildaid, ?), siteName: coalesce(site.name, ?), siteNumber: siteNumber} ELSE {scope: toLower(coalesce(d.tmfLevel, ?))} END) WHEN ? THEN attributesJson ELSE ? END, // accuracyCount: for Document Metadata, the number of keys whose // recommended value matches the actual value (?–N out of // recommendationCount). For all other categories, ? or ? (mirrors // ? or ? for other categories. NULL when not yet actioned. accuracyCount: CASE r.category WHEN ? THEN size([k IN keys(apoc.map.clean(apoc.convert.fromJsonMap(coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?)), [], [?])) WHERE apoc.map.clean(apoc.convert.fromJsonMap(coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?)), [], [?])[k] = apoc.map.clean(apoc.convert.fromJsonMap(coalesce(attributesJson, ?)), [], [?])[k]]) WHEN ? THEN size([k IN keys(cmpMap) WHERE (cmpAllowed IS NULL OR k IN cmpAllowed) AND coalesce(toString(cmpMap[k]), ?) <> ? AND toLower(coalesce(toString(cmpMap[k]), ?)) = toLower(coalesce(toString((CASE toLower(coalesce(d.tmfLevel, ?)) WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), country: coalesce(d.country, ?)} WHEN ? THEN {scope: toLower(coalesce(d.tmfLevel, ?)), siteId: coalesce(site.tildaid, ?), siteName: coalesce(site.name, ?), siteNumber: siteNumber} ELSE {scope: toLower(coalesce(d.tmfLevel, ?))} END)[k]), ?))]) ELSE CASE (CASE r.category WHEN ? THEN coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?) = coalesce(d.fileName, ?) WHEN ? THEN coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?) = ( apoc.text.lpad(toString(COALESCE(zone.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(section.number, ?)), ?, ?) + ? + apoc.text.lpad(toString(COALESCE(artifact.number, ?)), ?, ?) + ? + coalesce(artifact.name, ?)) WHEN ? THEN coalesce( CASE WHEN coalesce(r.orig_data,?) <> ? THEN r.orig_data END, CASE WHEN coalesce(r.data_formatted,?) <> ? THEN r.data_formatted END, r.data, ?) = coalesce(subArtifact.name, ?) ELSE CASE WHEN r.category IN [?, ?, ?, ?] THEN r.user_action = ? OR (r.user_action = ? AND coalesce(r.comment, ?) = ?) END END) WHEN true THEN ? WHEN false THEN ? ELSE null END END, comment: r.comment, statePromotedBy: statePromotedBy, statePromotedByName: statePromotedByName } ) YIELD node RETURN node
write dashrsvc
16 51 ms 600 ms 649 ms flat
match (a:Actor) where a.tildaid in $actorIds optional match (a)-[:STUDYSITEACTOR_TO_ACTOR]-(sp:StudySiteActor{delpending:false})- [:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)- [:PLAN_DEFINITION_TO_SITE]-(s:Site) where s.tild…
match (a:Actor) where a.tildaid in $actorIds optional match (a)-[:STUDYSITEACTOR_TO_ACTOR]-(sp:StudySiteActor{delpending:false})- [:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)- [:PLAN_DEFINITION_TO_SITE]-(s:Site) where s.tildaid in $siteIds call { with a,s,pd,sp match (r:Role) where r.name in sp.roles optional match (a)-[primaryRole:IS_PRIMARY_ROLE]-(r) with collect(distinct {name:r.name,displayName:r.displayName,assignDuty:r.assignDuty, tag:r.tags, isPrimary: CASE WHEN primaryRole IS NOT NULL THEN true ELSE false END}) as roleList,s,pd,a,sp optional match (sp)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]- (studySiteDuty:StudySiteDuty{removed:false,delpending:false})- [:DUTY_TO_STUDYSITEDUTY]-(duty:Duty) WITH a,s,pd,sp,duty,studySiteDuty,roleList ORDER BY duty.number with a,s,pd,sp,collect({duty: duty, studySiteDuty: studySiteDuty}) as duties,roleList with a,collect({duties:duties,siteId:s.tildaid,siteName:s.name,siteNumber:pd.siteNumber,country:s.country,roles:roleList,location:sp.location,ssaId:sp.tildaid}) as sites return collect({tildaid:a.tildaid, sites: sites}) as s1 } with s1,a call { with a with a optional match(a)-[:APPOINTED_AS]-(r:Role) optional match(a)-[primaryRole:IS_PRIMARY_ROLE]-(r) with collect(distinct {name:r.name,displayName:r.displayName,assignDuty:r.assignDuty,tag:r.tags,isPrimary: CASE WHEN primaryRole IS NOT NULL THEN true ELSE false END}) as roleList,a optional match (a)-[:STUDYSITEACTOR_TO_ACTOR]-(siteActor:StudySiteActor{delpending:false})- [:STUDYSITEDUTY_TO_STUDYSITEACTOR]-(studySiteDuty:StudySiteDuty{removed:false,delpending:false})- [:DUTY_TO_STUDYSITEDUTY]-(duty:Duty) WITH a, duty, studySiteDuty, roleList ORDER BY duty.number with a,collect({duty: duty, studySiteDuty: studySiteDuty}) as duties,roleList with a,collect({duties:duties,roles:roleList}) as sites return collect({tildaid:a.tildaid, sites: sites}) as s2 } return case when size(s1)>? then s1 else s2 end order by a.createdat
execsvc
1 327 ms 485 ms 327 ms flat
MATCH (o:Study{tildaid:$scopeId})-[scope:STUDY_TO_ACTOR]->(a:Actor{delpending:false})-[:FOR_USER]->(u:User) WHERE u.status<>? and a.state<>? MATCH (a)-[:APPOINTED_AS]->(r:Role) WHERE r.name <> ? MATCH (filterSite:Site{orgId:a.orgId}) WHERE…
MATCH (o:Study{tildaid:$scopeId})-[scope:STUDY_TO_ACTOR]->(a:Actor{delpending:false})-[:FOR_USER]->(u:User) WHERE u.status<>? and a.state<>? MATCH (a)-[:APPOINTED_AS]->(r:Role) WHERE r.name <> ? MATCH (filterSite:Site{orgId:a.orgId}) WHERE filterSite.tildaid in $filterSiteIds AND EXISTS { MATCH (a)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(spd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(filterSite), (spd)-[:PLAN_DEFINITION_TO_STUDY]-(o) } optional match (co:ClinOp{deltimestamp:?, orgId:$orgId})-[:FOR_USER]-(u) with distinct o,a,r,u,scope,co where ( toString(u.status) = $actorFilter0 ) with o,a,u,scope,r,co order by r.displayName with o,a,u,scope,co,collect(distinct case when r.listed = true then r.displayName end) as displayRoles with o,a,u,scope,displayRoles,co order by a.createdat, a.tildaid limit $qpLimit RETURN apoc.create.vNode([?], { id: a.tildaid, role: a.roles, displayRoles: CASE WHEN size(displayRoles) = ? THEN [?] ELSE displayRoles END, state: a.state, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, userEmail: case when coalesce(co.actorEmail,?)<>? then co.actorEmail else u.email end, userId: u.tildaid, userName: case when coalesce(co.actorName,?)<>? then co.actorName else coalesce(u.name, u.email) end, userContact: case when coalesce(co.actorPhone,?)<>? then co.actorPhone else coalesce(u.contact,?) end, lastActivity: u.lastActivity, profilePicURL: u.profilePicURL, userTitle: u.title, createdat: scope.createdat, isBlinded: a.isBlinded , capabilities: scope.capabilities, applyToStudyRoleFlag: scope.applyToStudyRoleFlag }) as node
write execsvc
1 398 ms 485 ms 398 ms flat
match (a:Actor) where a.tildaid in $actorIds optional match (a)-[:STUDYSITEACTOR_TO_ACTOR]-(sp:StudySiteActor{delpending:false})- [:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)- [:PLAN_DEFINITION_TO_SITE]-(s:Site) where s.tild…
match (a:Actor) where a.tildaid in $actorIds optional match (a)-[:STUDYSITEACTOR_TO_ACTOR]-(sp:StudySiteActor{delpending:false})- [:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)- [:PLAN_DEFINITION_TO_SITE]-(s:Site) where s.tildaid in $siteIds call { with a,s,pd,sp match (r:Role) where r.name in sp.roles optional match (a)-[primaryRole:IS_PRIMARY_ROLE]-(r) with collect(distinct {name:r.name,displayName:r.displayName,assignDuty:r.assignDuty, tag:r.tags, isPrimary: CASE WHEN primaryRole IS NOT NULL THEN true ELSE false END}) as roleList,s,pd,a,sp optional match (sp)-[:STUDYSITEDUTY_TO_STUDYSITEACTOR]- (studySiteDuty:StudySiteDuty{removed:false,delpending:false})- [:DUTY_TO_STUDYSITEDUTY]-(duty:Duty) WITH a,s,pd,sp,duty,studySiteDuty,roleList ORDER BY duty.number with a,s,pd,sp,collect({duty: duty, studySiteDuty: studySiteDuty}) as duties,roleList with a,collect({duties:duties,siteId:s.tildaid,siteName:s.name,siteNumber:pd.siteNumber,country:s.country,roles:roleList,location:sp.location,ssaId:sp.tildaid}) as sites return collect({tildaid:a.tildaid, sites: sites}) as s1 } with s1,a call { with a with a optional match(a)-[:APPOINTED_AS]-(r:Role) optional match(a)-[primaryRole:IS_PRIMARY_ROLE]-(r) with collect(distinct {name:r.name,displayName:r.displayName,assignDuty:r.assignDuty,tag:r.tags,isPrimary: CASE WHEN primaryRole IS NOT NULL THEN true ELSE false END}) as roleList,a optional match (a)-[:STUDYSITEACTOR_TO_ACTOR]-(siteActor:StudySiteActor{delpending:false})- [:STUDYSITEDUTY_TO_STUDYSITEACTOR]-(studySiteDuty:StudySiteDuty{removed:false,delpending:false})- [:DUTY_TO_STUDYSITEDUTY]-(duty:Duty) WITH a, duty, studySiteDuty, roleList ORDER BY duty.number with a,collect({duty: duty, studySiteDuty: studySiteDuty}) as duties,roleList with a,collect({duties:duties,roles:roleList}) as sites return collect({tildaid:a.tildaid, sites: sites}) as s2 } return case when size(s1)>? then s1 else s2 end order by a.createdat
execsvc
1 320 ms 485 ms 320 ms flat
MATCH (o:Study{tildaid:$scopeId})-[scope:STUDY_TO_ACTOR]->(a:Actor{delpending:false})-[:FOR_USER]->(u:User) WHERE u.status<>? and a.state<>? MATCH (a)-[:APPOINTED_AS]->(r:Role) WHERE r.name <> ? MATCH (filterSite:Site{orgId:a.orgId}) WHERE…
MATCH (o:Study{tildaid:$scopeId})-[scope:STUDY_TO_ACTOR]->(a:Actor{delpending:false})-[:FOR_USER]->(u:User) WHERE u.status<>? and a.state<>? MATCH (a)-[:APPOINTED_AS]->(r:Role) WHERE r.name <> ? MATCH (filterSite:Site{orgId:a.orgId}) WHERE filterSite.tildaid in $filterSiteIds AND EXISTS { MATCH (a)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor{delpending:false})-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(spd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(filterSite), (spd)-[:PLAN_DEFINITION_TO_STUDY]-(o) } optional match (co:ClinOp{deltimestamp:?, orgId:$orgId})-[:FOR_USER]-(u) with distinct o,a,r,u,scope,co where ( toString(u.status) = $actorFilter0 ) with o,a,u,scope,r,co order by r.displayName with o,a,u,scope,co,collect(distinct case when r.listed = true then r.displayName end) as displayRoles RETURN count(a)
execsvc
1 447 ms 485 ms 447 ms flat
MATCH (d:Document) WHERE coalesce(d.docOriginMethod, ?) = ? WITH d LIMIT $batchSize CALL { WITH d OPTIONAL MATCH (d)-[:DOCUMENT_VERSION]->(dv:DocumentVersion) WITH d, dv ORDER BY dv.majorVersion, dv.minorVersion, dv.createdat WITH d, head(…
MATCH (d:Document) WHERE coalesce(d.docOriginMethod, ?) = ? WITH d LIMIT $batchSize CALL { WITH d OPTIONAL MATCH (d)-[:DOCUMENT_VERSION]->(dv:DocumentVersion) WITH d, dv ORDER BY dv.majorVersion, dv.minorVersion, dv.createdat WITH d, head(collect(dv)) AS firstVer OPTIONAL MATCH (firstVer)-[:UPLOADED_BY]->(vu:User) OPTIONAL MATCH (d)-[:UPLOADED_BY]->(du:User) OPTIONAL MATCH (d)<-[:INCOMING_EMAIL_TO_DOCUMENT]-(ie:IncomingEmail) WITH d, firstVer, coalesce(head(collect(vu)), head(collect(du))) AS u, head(collect(ie)) AS ie OPTIONAL MATCH (firstVer)-[:ALL_CHANGELOG]->(c:ChangeLog) WHERE firstVer.uploadMethod = $emailMethod AND c.description CONTAINS $emailUploadPhrase WITH d, firstVer, u, ie, [g IN collect(apoc.text.regexGroups(c.description, $senderPattern)) WHERE size(g) > ? AND g[?][?] <> $botEmail | g[?][?]] AS textSenders WITH d, u, ie, textSenders, trim(coalesce(ie.sender, ?)) AS emailSender, CASE WHEN firstVer IS NOT NULL THEN firstVer.uploadMethod ELSE d.uploadMethod END AS rawMethod, CASE WHEN coalesce(firstVer.createdat, ?) > ? THEN firstVer.createdat ELSE coalesce(d.createdat, ?) END AS firstAt WITH d, u, ie, textSenders, emailSender, firstAt, CASE WHEN emailSender <> ? THEN $emailMethod WHEN rawMethod IN $uploadMethods THEN rawMethod WHEN d.docKind = $liveKind THEN $liveMethod ELSE $manualMethod END AS method RETURN method, CASE WHEN emailSender <> ? THEN emailSender WHEN size(textSenders) > ? THEN textSenders[?] WHEN method = $emailMethod AND coalesce(u.email, ?) = $botEmail THEN ? WHEN trim(coalesce(u.name, ?)) <> ? THEN trim(u.name) ELSE trim(coalesce(u.email, ?)) END AS sender, CASE WHEN emailSender <> ? AND coalesce(ie.received_at_ms, ?) > ? THEN ie.received_at_ms ELSE firstAt END AS at } SET d.docOriginMethod = method, d.docOriginSender = sender, d.docOriginAt = at RETURN count(d)
write docsvc
1 219 ms 485 ms 219 ms flat
MATCH (dv:DocumentVersion) WHERE dv.originalFileName IS NULL WITH dv LIMIT $batchSize OPTIONAL MATCH (d:Document)-[:DOCUMENT_VERSION]->(dv) CALL { WITH dv, d OPTIONAL MATCH (dv)-[:ALL_CHANGELOG]->(vch:ChangeLog) WHERE vch.oldValue CONTAINS…
MATCH (dv:DocumentVersion) WHERE dv.originalFileName IS NULL WITH dv LIMIT $batchSize OPTIONAL MATCH (d:Document)-[:DOCUMENT_VERSION]->(dv) CALL { WITH dv, d OPTIONAL MATCH (dv)-[:ALL_CHANGELOG]->(vch:ChangeLog) WHERE vch.oldValue CONTAINS ? WITH dv, d, collect(vch) AS vchs OPTIONAL MATCH (d)-[:ALL_CHANGELOG]->(dch:ChangeLog) WHERE d IS NOT NULL AND dch.oldValue CONTAINS ? AND NOT EXISTS { MATCH (d)-[:DOCUMENT_VERSION]->(prev:DocumentVersion) WHERE coalesce(prev.versionNumber, ?) < coalesce(dv.versionNumber, ?) } WITH vchs, collect(dch) AS dchs WITH [ch IN (vchs + dchs) WHERE ch IS NOT NULL] AS chs UNWIND CASE WHEN size(chs) = ? THEN [null] ELSE chs END AS ch WITH ch ORDER BY ch.createdat ASC, CASE WHEN ch.oldValue CONTAINS ? THEN ? ELSE ? END WITH head(collect(ch)) AS clog RETURN CASE WHEN clog IS NULL OR clog.oldValue IS NULL OR NOT (clog.oldValue STARTS WITH ?) THEN ? ELSE coalesce( apoc.convert.fromJsonMap(clog.oldValue)[?], apoc.convert.fromJsonMap(clog.oldValue)[?], ? ) END AS originalFileName } SET dv.originalFileName = originalFileName RETURN count(dv)
write docsvc
1 217 ms 485 ms 217 ms flat
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_…
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl) ) and not exists ( (task)<-[:CREATED_FROM_SPEC]-(:ParticipantDslSpecNode)<-[:PARTICIPANT_DSLTASK_TO_ALL_PARTICIPANT_DSLSPECNODE]-(:ParticipantDslTask)<-[:PARTICIPANT_DSLTASK]-(:ParticipantControl) ) match (task)-[:WITH_CONTEXT]->() optional match (task)-[:BELONGS_TO]->(userinbox:Inbox{type:?})-[:OWNED_BY]->(user:User) optional match (user)-[:USER_TO_PATIENT]-(patient:Patient) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(org:Organization) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(study:Study) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(site:Site) return distinct task, userinbox, org, study.name, site.name, patient.tildaid skip $skip limit $limit
tusker
1 319 ms 485 ms 319 ms flat
MATCH (wo:WorkOrder {tildaid: $id}) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_ORGANIZATION]->(org:Organization) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_STUDY]->(study:Study) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_SITE]->(site:Site) // Parent WorkOrderC…
MATCH (wo:WorkOrder {tildaid: $id}) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_ORGANIZATION]->(org:Organization) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_STUDY]->(study:Study) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_SITE]->(site:Site) // Parent WorkOrderConfig (WorkOrder.parent_id -> WorkOrderConfig). Null for standalone WOs. // Carries the ENG-? ? timing, denormalized onto each child WO below. OPTIONAL MATCH (cfg:WorkOrderConfig {tildaid: wo.parent_id}) // Template lineage via the same config. Null for ad-hoc configs (no template). OPTIONAL MATCH (cfg)-[:WORK_ORDER_CONFIG_TO_TEMPLATE]->(tmpl:WorkOrderTemplate) CALL apoc.create.vNode([?], { tildaid: wo.tildaid, workOrderId: wo.work_order_id, workOrderType: wo.work_order_type, title: wo.title, status: wo.status, priority: wo.priority, goal: wo.goal, emailSubject: wo.email_subject, summary: wo.summary, forms: wo.forms, creatorId: wo.creator_id, assignedToId: wo.assigned_to_id, resolvedBy: wo.resolved_by, parentId: wo.parent_id, eventId: wo.event_id, templateId: tmpl.tildaid, templateName: tmpl.title, threadId: wo.thread_id, rfcMsgId: wo.rfc_msgid, createdat: wo.createdat, updatedat: wo.updatedat, lastReachout: wo.last_reachout, lastResponse: wo.last_response, dueDate: wo.due_date, resolvedAt: wo.resolved_at, statusUpdatedAt: wo.status_updated_at, activatedAt: wo.activated_at, // ENG-? ? timing, denormalized from the parent WorkOrderConfig (cfg). // configActivatedAt is distinct from activatedAt above (the child WO?t trip Neo4j?RECEIVED?RECEIVED?Received?Accepted?SENT?RECEIVED?RECEIVED' | wd]), contactList: wo.contact_list, ccList: wo.cc_list, delpending: wo.delpending, deltimestamp: wo.deltimestamp, holdbitmap: wo.holdbitmap, orgId: coalesce(org.tildaid, wo.org_id), orgName: org.name, orgAlias: org.alias, studyId: coalesce(study.tildaid, wo.study_id), studyName: study.name, siteId: coalesce(site.tildaid, wo.site_id), siteName: site.name } ) YIELD node RETURN node
write dashrsvc
1 201 ms 485 ms 201 ms flat
MATCH (a:WorkOrderActivity {tildaid: $id}) WHERE a.activity_type <> ? OPTIONAL MATCH (wo:WorkOrder)-[:WORK_ORDER_TO_ACTIVITY]->(a) WITH a, head(collect(wo)) AS wo OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_ORGANIZATION]->(org:Organization) OPTION…
MATCH (a:WorkOrderActivity {tildaid: $id}) WHERE a.activity_type <> ? OPTIONAL MATCH (wo:WorkOrder)-[:WORK_ORDER_TO_ACTIVITY]->(a) WITH a, head(collect(wo)) AS wo OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_ORGANIZATION]->(org:Organization) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_STUDY]->(study:Study) OPTIONAL MATCH (wo)-[:WORK_ORDER_TO_SITE]->(site:Site) // Template lineage of the parent WO (parent_id -> WorkOrderConfig -> WorkOrderTemplate), denormalized. OPTIONAL MATCH (:WorkOrderConfig {tildaid: wo.parent_id})-[:WORK_ORDER_CONFIG_TO_TEMPLATE]->(tmpl:WorkOrderTemplate) CALL apoc.create.vNode([?], { tildaid: a.tildaid, workOrderActivityId: a.tildaid, workOrderId: a.work_order_id, userId: a.user_id, activityType: a.activity_type, status: a.status, source: a.source, content: a.content, original_content: a.original_content, reasoning: a.reasoning, content_type: a.content_type, body_plain: a.body_plain, body_html: a.body_html, stripped_text: a.stripped_text, stripped_html: a.stripped_html, extra_text: a.extra_text, extra_html: a.extra_html, message_id: a.message_id, sender: a.sender, from_ai_teammate_email: a.from_ai_teammate_email, from_ai_teammate_name: a.from_ai_teammate_name, attachmentInfo: a.attachmentInfo, ignoreType: a.ignoreType, auto_send_enabled: a.auto_send_enabled, documents_pending: a.documents_pending, processing_status: a.processing_status, retry_count: a.retry_count, last_attempt_at: a.last_attempt_at, openai_response_id: a.openai_response_id, errors: a.errors, questions: a.questions, answers: a.answers, answered_at: a.answered_at, agent_run_completed_at: a.agent_run_completed_at, timestamp: a.timestamp, createdat: a.createdat, updatedat: a.updatedat, // Parent work order context: immutable created timestamp (epoch millis) + business id, denormalized // so time-to-response style metrics can be computed from the activities table alone. workOrderCreatedAt: wo.createdat, workOrderBusinessId: wo.work_order_id, workOrderName: wo.title, templateId: tmpl.tildaid, templateName: tmpl.title, docIds: a.docIds, processedDocIds: a.processedDocIds, activity_contact_list: a.activity_contact_list, activity_cc_list: a.activity_cc_list, delpending: a.delpending, deltimestamp: a.deltimestamp, holdbitmap: a.holdbitmap, orgId: coalesce(org.tildaid, wo.org_id), orgName: org.name, orgAlias: org.alias, studyId: coalesce(study.tildaid, wo.study_id), studyName: study.name, siteId: coalesce(site.tildaid, wo.site_id), siteName: site.name } ) YIELD node RETURN node
write dashrsvc
1 240 ms 485 ms 240 ms flat
MATCH (e:Event {tildaid: $eventId, orgId: $orgId})- [:REQUIRES_RESOLUTION_OF]->(d:Deliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document) WHERE COALESCE(tracksRel.state, ?) in $userDecisions OPTIONAL MATCH (ph)-[:SCOPED_TO]->(es:EventSc…
MATCH (e:Event {tildaid: $eventId, orgId: $orgId})- [:REQUIRES_RESOLUTION_OF]->(d:Deliverable)-[tracksRel:TRACKS_PLACEHOLDER]->(ph:Document) WHERE COALESCE(tracksRel.state, ?) in $userDecisions OPTIONAL MATCH (ph)-[:SCOPED_TO]->(es:EventScope)-[:PART_OF_EVENT]->(e) OPTIONAL MATCH (wo:WorkOrder)-[woClaim:WO_CLAIMS_PLACEHOLDER]->(ph) WHERE woClaim.is_current = true WITH e, ph, d, es, tracksRel, head(collect(wo)) AS wo OPTIONAL MATCH (ph)-[acceptedRel:ACCEPTED_DOCUMENT]->(acceptedDoc:Document) OPTIONAL MATCH (ph)-[matchRel:MATCHING_DOCUMENT]->(matchDoc:Document) WHERE acceptedDoc IS NULL OR matchDoc.tildaid <> acceptedDoc.tildaid WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND matchDoc.collection = ? THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as matchesExpectedCollected, collect(DISTINCT CASE WHEN matchDoc IS NOT NULL AND (matchDoc.collection IS NULL OR matchDoc.collection <> ?) THEN { documentId: matchDoc.tildaid, displayId: matchDoc.displayId, contentType: matchDoc.contentType, documentName: matchDoc.fileName, documentDate: CASE WHEN matchDoc.createdat IS NOT NULL THEN datetime({epochMillis: matchDoc.createdat}) ELSE null END, isAccepted: false, matchedBy: matchRel.matchedBy, matchedAt: CASE WHEN matchRel.createdat IS NOT NULL THEN datetime({epochMillis: matchRel.createdat}) ELSE null END, docAttributes: [ (matchDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: matchDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } END) as pendingMatchesCollected WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, [m IN matchesExpectedCollected WHERE m IS NOT NULL] as matchesExpected, [m IN pendingMatchesCollected WHERE m IS NOT NULL] as pendingMatches WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, CASE WHEN ph.status <> ? THEN ? WHEN acceptedDoc IS NOT NULL THEN ? WHEN SIZE(matchesExpected) > ? AND SIZE(pendingMatches) = ? THEN ? WHEN SIZE(matchesExpected) > ? OR SIZE(pendingMatches) > ? THEN ? ELSE ? END as matchStatus OPTIONAL MATCH (ph)-[renewalRel:RENEWAL_OF]->(renewalSrcCandidate:Document) WHERE coalesce(renewalRel.primary, false) = true AND coalesce(renewalSrcCandidate.archived, false) = false WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, head(collect(renewalSrcCandidate)) AS renewalSrc OPTIONAL MATCH (e)<-[:EVENTS]-(:EventPlan)-[:PLAN_CONTEXT]->(study) OPTIONAL MATCH (site:Site {tildaid: es.scopeId}) OPTIONAL MATCH (site)-[:PLAN_DEFINITION_TO_SITE]-(pd:DslPlanDefinition {type: ?})-[:PLAN_DEFINITION_TO_STUDY]->(study) WITH e, ph, d, es, tracksRel, acceptedDoc, acceptedRel, wo, matchesExpected, pendingMatches, matchStatus, renewalSrc, COALESCE(head(collect(site.name)), ?) AS siteName, COALESCE(head(collect(pd.siteNumber)), ?) AS siteNumber ORDER BY d.name ASC, ph.fileName ASC WITH collect({ placeholderId: ph.tildaid, placeholderName: ph.fileName, deliverableId: d.tildaid, deliverableName: d.name, eventScopeId: es.tildaid, scopeType: es.scopeType, country: CASE WHEN es.scopeType = ? THEN COALESCE(es.scopeId, ?) ELSE ? END, siteNumber: siteNumber, siteName: siteName, dueDate: es.dueDate, matchStatus: matchStatus, userDecision: COALESCE(tracksRel.state, ?), archived: coalesce(ph.archived, false) OR es.status = ?, origin: COALESCE(tracksRel.origin, ?), feedback: COALESCE(tracksRel.feedback, ?), reason: COALESCE(tracksRel.reason, ?), removalHasAcceptedDocument: COALESCE(tracksRel.removalHasAcceptedDocument, false), workOrder: CASE WHEN wo IS NOT NULL THEN {tildaid: wo.tildaid, work_order_id: wo.work_order_id, status: wo.status} ELSE null END, renewalSource: CASE WHEN renewalSrc IS NOT NULL THEN {documentId: renewalSrc.tildaid, documentName: renewalSrc.fileName} ELSE null END, acceptedMatch: CASE WHEN acceptedDoc IS NOT NULL THEN { documentId: acceptedDoc.tildaid, displayId: acceptedDoc.displayId, contentType: acceptedDoc.contentType, documentName: acceptedDoc.fileName, documentDate: CASE WHEN acceptedDoc.createdat IS NOT NULL THEN datetime({epochMillis: acceptedDoc.createdat}) ELSE null END, isAccepted: true, matchedBy: COALESCE(head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | amr.matchedBy ]), ?), matchedAt: head([ (ph)-[amr:MATCHING_DOCUMENT]->(acceptedDoc) | CASE WHEN amr.createdat IS NOT NULL THEN datetime({epochMillis: amr.createdat}) ELSE null END ]), acceptedBy: acceptedRel.acceptedBy, acceptedAt: CASE WHEN acceptedRel.createdat IS NOT NULL THEN datetime({epochMillis: acceptedRel.createdat}) ELSE null END, docAttributes: [ (acceptedDoc)-[:DOCUMENT_TO_SOURCE]->(daSrc:DocumentSource)-[:HAS_ATTRIBUTE]->(da:DocAttribute) WHERE (daSrc)-[:DOCUMENT_SOURCE_TO_VERSION]->(:DocumentVersion {tildaid: acceptedDoc.primaryVersionId}) | {key: da.key, value: da.value, type: da.type, name: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.name ]), ?), description: COALESCE(head([ (da)-[:HAS_ATTRIBUTE_DEFINITION]->(dad:DocAttributeDefinition) | dad.description ]), ?)} ] } ELSE null END, matchesExpected: matchesExpected, pendingMatches: pendingMatches }) as allRows WITH allRows, allRows as filteredRows WITH allRows, filteredRows, { total: size(allRows), pending: size([pp IN allRows WHERE pp.userDecision IN [?, ?]]), pendingForDelete: size([pp IN allRows WHERE pp.userDecision = ?]), accepted: size([pp IN allRows WHERE pp.userDecision = ?]), rejected: size([pp IN allRows WHERE pp.userDecision = ?]), incomplete: size([pp IN allRows WHERE pp.matchStatus = ?]), potentialMatchFound: size([pp IN allRows WHERE pp.matchStatus = ?]), matchExpected: size([pp IN allRows WHERE pp.matchStatus = ?]), matched: size([pp IN allRows WHERE pp.matchStatus = ?]), finalized: size([pp IN allRows WHERE pp.matchStatus = ?]), completion: {required: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]), completed: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]), aiSuggested: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), inStaging: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), noMatchFound: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus = ?]), notConfirmed: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND pp.userDecision = ? AND NOT pp.matchStatus = ?]), removed: size([pp IN allRows WHERE (pp.userDecision = ? OR coalesce(pp.archived, false))]), percentComplete: CASE WHEN size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]) = ? THEN null ELSE toInteger(floor(?.? * size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]) / size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]))) END, isComplete: size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)]) > ? AND size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?) AND pp.matchStatus IN [?, ?]]) = size([pp IN allRows WHERE NOT (pp.userDecision = ? OR coalesce(pp.archived, false)) AND (pp.userDecision IN [?, ?] OR pp.matchStatus = ?)])}, matchedWithNew: size([pp IN allRows WHERE pp.matchStatus = ? AND (size(pp.pendingMatches) > ? OR size(pp.matchesExpected) > ?)]) } as progress RETURN filteredRows[$pageSkip..$pageEnd] as placeholders, size(filteredRows) as total, progress
docsvc
1 204 ms 485 ms 204 ms flat
match(:User{email:$staffEmail})-[]-(:Staff)-[:W2026_46 | W2026_47]-(r:ResourceAppointment) where (isEmpty(r.type) or r.type=?) optional match (r)-[:RESOURCE_APPOINTMENTS]-(v:VisitAppointment)-[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(:CarePlan)-[…
match(:User{email:$staffEmail})-[]-(:Staff)-[:W2026_46 | W2026_47]-(r:ResourceAppointment) where (isEmpty(r.type) or r.type=?) optional match (r)-[:RESOURCE_APPOINTMENTS]-(v:VisitAppointment)-[:VISIT_APPOINTMENT_TO_CARE_PLAN]-(:CarePlan)-[:RESEARCH_SUBJECT]-(:Patient)-[:HAS_PROTECTED_DATA_IN]-(p:Phi) optional match (v)-[]-(s:Site)-[:SITE_TO_SMO]->(smo:Smo)-[:IS_AN_ORG]->(o:Organization{alias:$orgAlias}) optional match (v)-[:VISIT_APPOINTMENT_TO_CONFERENCE_DATA]-(c:ConferenceData) return r,p,c,v,s.tildaid
schedsvc
1 276 ms 485 ms 276 ms flat
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- …
MATCH (pv:ParticipantVisit) WHERE (pv.edcStatus IS NULL OR pv.edcStatus = ? OR pv.edcStatus = ? OR pv.edcStatus = ?) AND (pv.status = ? OR pv.status = ?) AND pv.deltimestamp = ? MATCH (cp:CarePlan {tildaid: pv.carePlanId})-[:EXECUTE_SOA]- (soa:ScheduleOfActivities)-[:SOA_TO_PLAN_DEFINITION]- (pd:DslPlanDefinition) MATCH (pd)-[:PLAN_DEFINITION_TO_STUDY]-(st:Study) MATCH (pd)-[:PLAN_DEFINITION_TO_SITE]-(site:Site)-[:SITE_TO_SMO]- (smo)-[:IS_AN_ORG]-(org:Organization) OPTIONAL MATCH (visit:VisitV2)-[:BELONGS_TO_VISIT]-(pv) WITH pv, cp, st, site, org, visit OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(emptyPa:ParticipantActivity) WHERE (emptyPa.edcStatus IS NULL OR emptyPa.edcStatus = ?) AND emptyPa.deltimestamp = ? WITH pv, cp, st, site, org, visit, collect(emptyPa) AS emptyPas OPTIONAL MATCH (pv)-[:PARTICIPANT_ACTIVITY]->(pa:ParticipantActivity)-[:BELONGS_TO_ACTIVITY]->(act:ActivityV2) WHERE pa.deltimestamp = ? RETURN pv, cp, st, site, org, visit, emptyPas, collect({pa: pa, activity: act}) AS paActPairs LIMIT ?
execsvc
3 437 ms 485 ms 479 ms flat
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather th…
MATCH (org:Organization{tildaid:$scopeOrgId0}) CALL { WITH org UNWIND $lists.allowedStudies0 AS studyId OPTIONAL MATCH (study:Study) WHERE study.tildaid = studyId // An inactive actor carries no membership, so it is excluded here rather than at each use: // the sites, countries and roles below all hang off it. OPTIONAL MATCH (study)-[:STUDY_TO_ACTOR]-(studyActor:Actor{orgId:$ctxOrgId0})-[:FOR_USER]->(:User{tildaid:$ctxUserId0}) WHERE studyActor.state <> ? // Collect sites where user has membership in this study OPTIONAL MATCH (studyActor)-[:STUDYSITEACTOR_TO_ACTOR]-(:StudySiteActor)-[:STUDYSITEACTOR_TO_PLAN_DEFINITION]-(pd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) WITH org, study, studyActor, collect(DISTINCT site.tildaid) as userSiteIds // Collect countries where user has membership in this study OPTIONAL MATCH (studyActor)<-[:STUDYCOUNTRYACTOR_TO_ACTOR]-(sca:StudyCountryActor) WITH org, study, studyActor, userSiteIds, collect(DISTINCT sca.country) as userCountryIds // Pre-collect policies for this user/role in this study OPTIONAL MATCH (org)<-[:DOC_ACCESS_POLICY_TO_ORG]-(policy:DocumentAccessPolicy{status:?})-[:POLICY_TO_STUDY]->(study) WHERE EXISTS { MATCH (policy)-[:POLICY_FOR_USER]->(:User{tildaid:$ctxUserId0}) } OR (studyActor IS NOT NULL AND EXISTS { MATCH (policy)-[:POLICY_FOR_ROLE]->(role:Role)<-[:APPOINTED_AS]-(studyActor) }) WITH study, studyActor, userSiteIds, userCountryIds, collect(policy) as policies WITH {study:study, studyActor:studyActor, userSiteIds:userSiteIds, userCountryIds:userCountryIds, policies:policies} AS st WITH collect(distinct st) as studies // studyById lets each document row look its context up by study id instead of scanning // the whole list. Entries whose study did not resolve share the ? key; they all carry // the same empty context, so which one wins does not matter. The node-bearing maps go no // further than this subquery; studyIdsInScope needs no lookup, since an id with no Study // node can never equal a matched study??ReadWrite?ReadOnly?s folder and ancestors (up to ? levels for TMF hierarchy) OPTIONAL MATCH (doc1)<-[:INCLUDES]-(docFolder:Folder) OPTIONAL MATCH (docFolder)<-[:INCLUDES*?..?]-(ancestorFolder:Folder) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, collect(DISTINCT docFolder) + collect(DISTINCT ancestorFolder) AS folderHierarchy // Check document ownership OPTIONAL MATCH (doc1)-[:UPLOADED_BY]->(uploader:User{tildaid:$policyUploaderId0}) WITH doc1, policies, userSiteIds, userCountryIds, defaultAccessLevel, folderHierarchy, uploader IS NOT NULL AS isOwnedByUser // Filter policies by: // ?. Folder: policy applies if folder is in hierarchy OR policy has no folder (study-wide). A folder-less // ownership policy is the role?Owned Documents?Non Owned Documents?Staging?Pre-Staging?Owned Documents?Non Owned Documents?My Sites Documents?Other Sites Documents?My Countries Documents?Other Countries Documents?Study Scope Documents?NoAccess?NoAccess?ReadOnly?ReadOnly?ReadWrite?ReadOnly?NoAccess??Inactive? ?Received?Quality Control?PHI Issues???/???/???/') ELSE ? END as childFolderPath WITH docObj, ct, child, st, head(collect(childFolderPath)) AS childFolderPath WITH child, CASE WHEN ct IS NULL THEN null ELSE { documentId: child.tildaid, studyId: COALESCE(child.studyId, ?), studyName: COALESCE(st.name, ?), folderPath: childFolderPath, kind: ct.kind, stale: ct.sourceVersionId <> docObj.primaryVersionId, version: COALESCE(child.versionName, toString(child.majorVersion)) } END as copiedToEntry RETURN collect(copiedToEntry) as copiedTo, count(DISTINCT child) as copiedToCount } CALL { return null as promotedTo } CALL { return null AS docReviewStatus, null AS hasImportantComment, null AS discrepancyCount } CALL { return null as tmfCategorization } with distinct doc{.*, status: docObj.status, stateTag: docObj.stateTag, orgAlias: org.alias, owned: owned, docTypeName: doc.docTypeName, userId: doc.userId, user: CASE WHEN user IS NOT NULL THEN {id: user.tildaid, name: user.name, profilePicURL: user.profilePicURL} ELSE NULL END, createdDate: doc.createdat, updatedDate: COALESCE(doc.updatedat, doc.createdat), externalUrl: doc.url, study: study.name, studyId: study.tildaid, site: site.name, siteId: doc.siteId, siteNumber: siteNumber, piNames: piNames, patient: patientName, patientId: doc.patientId, subjectId: subjectId, visit: visit.title, visitId: doc.visitId, reviewDate: reviewDate, assignedToUsers: [], uploadedBy: {id: uploadedBy.tildaid, name: uploadedBy.name, profilePicURL: uploadedBy.profilePicURL}, signedByUsers: signedByUsers, tags: tags, listingFolders: listingFolders, primaryFolder: primaryFolder, latestAIRun: latestAIRun, findingCount: findingCount, queriesCount: queriesCount, confirmedCount: confirmedCount, resolvedCount: resolvedCount, notesCount: notesCount, stagingLabels: stagingLabels, workOrders: workOrders, woContact: COALESCE(woContact, false), stateTags1: stateTags1, stateTags2: stateTags2, docAccessLevel: docAccessLevel, isSigner: isSigner, docReviewStatus: docReviewStatus, hasImportantComment: hasImportantComment, discrepancyCount: discrepancyCount , templateDetails: templateDetails, docTypeDetails: docType{.*}, queries: queries, annotationSpec: annotationSpec, annotationVal: annotationVal, completedAt: annotationVal.completedAt, versions: versions, reviews: reviews, sharedWith: sharedWith, currentShare: currentShare, tmfCategorization: tmfCategorization, docAttributes: docAttributes, eventId: eventId, eventName: eventName, deliverable: deliverable, eventScope: eventScope, provenance: CASE WHEN copiedFrom IS NULL AND size(copiedTo) = ? THEN null ELSE {copiedFrom: copiedFrom, copiedTo: [x IN copiedTo WHERE x IS NOT NULL | x], copiedToCount: copiedToCount} END, promotedTo: promotedTo} AS document, total return document, total
docsvc
3 381 ms 485 ms 386 ms flat
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_…
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl) ) and not exists ( (task)<-[:CREATED_FROM_SPEC]-(:ParticipantDslSpecNode)<-[:PARTICIPANT_DSLTASK_TO_ALL_PARTICIPANT_DSLSPECNODE]-(:ParticipantDslTask)<-[:PARTICIPANT_DSLTASK]-(:ParticipantControl) ) match (task)-[:WITH_CONTEXT]->() optional match (task)-[:BELONGS_TO]->(userinbox:Inbox{type:?})-[:OWNED_BY]->(user:User) optional match (user)-[:USER_TO_PATIENT]-(patient:Patient) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(org:Organization) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(study:Study) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(site:Site) return distinct task, userinbox, org, study.name, site.name, patient.tildaid skip $skip limit $limit
tusker
37 187 ms 480 ms 717 ms flat
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
docsvc
8 248 ms 476 ms 423 ms flat
UNWIND $rows AS row MATCH (t:Task{tildaid:row.id}) SET t.dist_miss_count = row.count, t.dist_next_retry_at = row.nextRetryAt, t.dist_deferred = row.deferred
UNWIND $rows AS row MATCH (t:Task{tildaid:row.id}) SET t.dist_miss_count = row.count, t.dist_next_retry_at = row.nextRetryAt, t.dist_deferred = row.deferred
write tusker
2 180 ms 470 ms 255 ms flat
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
docsvc
4 182 ms 470 ms 304 ms flat
MATCH(lead:Lead {tildaid: $id}) MATCH (lead)-[:LEAD_TO_STUDY]-(study:Study) match (lead)-[:LEAD_TO_PATIENT]->(patient:Patient) MATCH(org:Organization) WHERE org.tildaid=study.srcorgid OPTIONAL MATCH (study)-[:PLAN_DEFINITION_TO_STUDY]-(dsl…
MATCH(lead:Lead {tildaid: $id}) MATCH (lead)-[:LEAD_TO_STUDY]-(study:Study) match (lead)-[:LEAD_TO_PATIENT]->(patient:Patient) MATCH(org:Organization) WHERE org.tildaid=study.srcorgid OPTIONAL MATCH (study)-[:PLAN_DEFINITION_TO_STUDY]-(dsl:DslPlanDefinition)-[:SOA_TO_PLAN_DEFINITION]-(:ScheduleOfActivities)-[:EXECUTE_SOA]-(:CarePlan)-[:RESEARCH_SUBJECT]-(patient) OPTIONAL MATCH (dsl)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) OPTIONAL MATCH (lead)-[:LEAD_TO_ADVOCATE]-(adv:Advocate)-[:ADV_TO_PORTAL_ORG]-(advOrg:AdvOrg) OPTIONAL MATCH (user:User{tildaid: adv.userid}) OPTIONAL MATCH (recruiterOrg:Organization{tildaid: adv.orgid}) CALL apoc.create.vNode([?], { studyName: study.name, orgId: org.tildaid, orgName: org.name, orgAlias: org.alias, siteId: site.tildaid, siteName: site.name, createdat: lead.createdat, updatedat: lead.updatedat, sourceType: lead.sourcetype, status: lead.status, leadId: lead.tildaid, patientId: lead.patientid, studyId: lead.studyid, recruiterOrg: recruiterOrg.name, recruiter: user.name, sex: patient.sex, ethnicity: patient.ethnicity } ) YIELD node RETURN node
write dashrsvc
2 124 ms 470 ms 247 ms flat
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor…
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor WITH u, u{.*, pinSet: pinSet, needs2Factor: needs2Factor} as profile CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, a, r order by r.displayName WITH u, o, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, orgType: o.orgType, status: o.status, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, role: roles, displayRoles: displayRoles, logoDocumentId: o.logoDocumentId, theme: o.theme, logoURL: o.logoURL, pinCheckEnabled: o.pinCheckEnabled }) AS organizations } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false,orgId:$orgId})<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false}) WITH DISTINCT s, a, u.capabilities AS caps CALL { WITH a OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH r order by r.displayName RETURN apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles } MATCH (s)-[:STUDY_TO_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) // DISTINCT restores the dedup the old (u,o,s,a) grouping gave for free: two parallel // STUDY_TO_ORG edges would otherwise emit the study twice. The ORDER BY makes the // studies array a stable sequence for clients rather than a plan-dependent one. WITH DISTINCT s, a, caps, roles, displayRoles, o ORDER BY s.name, s.tildaid, o.tildaid RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, studyName: s.name, studySponsor: s.sponsor, studyId: s.tildaid, startDate: a.startDate, endDate: a.endDate, capabilities: caps, iSBlinded: a.isBlinded, role: roles, displayRoles: displayRoles }) AS studies } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, siteId: si.tildaid, siteName: si.name, siteCity: si.city, contactPhone: si.contactPhone, contactEmail: si.contactEmail, defaultLanguage: si.defaultLanguage, role: roles, displayRoles: displayRoles }) as sites } return profile{.*, organizations: organizations, studies: studies, sites: sites}
tusker
2 141 ms 470 ms 240 ms flat
// The site???s log win date authority over this roster. UNWIND (CASE WHEN size(candNums) = ? THEN [null] ELSE candNums END) AS n OPTIONAL MATCH (pdAmb:DslPlanDefinition {studyId: $studyId})-[:PLAN_DEFINITION_TO_SITE]-(sAmb:Site) WHERE n I…
// The site???s log win date authority over this roster. UNWIND (CASE WHEN size(candNums) = ? THEN [null] ELSE candNums END) AS n OPTIONAL MATCH (pdAmb:DslPlanDefinition {studyId: $studyId})-[:PLAN_DEFINITION_TO_SITE]-(sAmb:Site) WHERE n IS NOT NULL AND trim(coalesce(pdAmb.siteNumber, ?)) = n AND coalesce(pdAmb.delpending, false) = false AND coalesce(sAmb.delpending, false) = false WITH n, size(collect(DISTINCT sAmb)) AS nSites // collect() drops nulls, so an ambiguous or unmatched number falls out here. WITH collect(CASE WHEN nSites = ? THEN n END) AS siteNums MATCH (d:Document {studyId: $studyId}) // coalesce(), not bare `= false` like DocumentFetcher.fetch: this list must not // silently drop a document that lacks the archived property entirely. WHERE coalesce(d.delpending, false) = false AND coalesce(d.archived, false) = false // An EDL placeholder (?) has no file to read. AND NOT EXISTS { (d)<-[:TRACKS_PLACEHOLDER]-() } AND ( d.siteId = $siteId OR ( (d.siteId IS NULL OR d.siteId = ?) AND trim(split(coalesce(d.fileName, ?), ?)[?]) <> ? AND trim(split(coalesce(d.fileName, ?), ?)[?]) IN siteNums ) ) OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(saT:SubArtifact) OPTIONAL MATCH (d)<-[:RECOMMENDATION_TO_DOCUMENT]-(saR:Recommendation) WHERE saR.category = ? AND coalesce(saR.delpending, false) = false WITH d, saT, saR ORDER BY coalesce(saR.createdat, ?) DESC WITH d, saT, head(collect(saR)) AS lastSaR WITH d, coalesce(saT.name, lastSaR.data) AS subArtifactName WHERE subArtifactName IN $doaNames // The metadata agent?Document Metadata??' WITH d, subArtifactName, md ORDER BY coalesce(md.createdat, ?) DESC WITH d, subArtifactName, head(collect(md)) AS lastMd RETURN d.tildaid AS docId, subArtifactName AS title, d.fileName AS fileName, d.primaryVersionId AS primaryVersionId, lastMd.data_formatted AS metadataJson, d.cksum AS cksum, coalesce(d.createdat, ?) AS createdAt
write ontologysvc
2 144 ms 470 ms 274 ms flat
// The site???s log win date authority over this roster. UNWIND (CASE WHEN size(candNums) = ? THEN [null] ELSE candNums END) AS n OPTIONAL MATCH (pdAmb:DslPlanDefinition {studyId: $studyId})-[:PLAN_DEFINITION_TO_SITE]-(sAmb:Site) WHERE n I…
// The site???s log win date authority over this roster. UNWIND (CASE WHEN size(candNums) = ? THEN [null] ELSE candNums END) AS n OPTIONAL MATCH (pdAmb:DslPlanDefinition {studyId: $studyId})-[:PLAN_DEFINITION_TO_SITE]-(sAmb:Site) WHERE n IS NOT NULL AND trim(coalesce(pdAmb.siteNumber, ?)) = n AND coalesce(pdAmb.delpending, false) = false AND coalesce(sAmb.delpending, false) = false WITH n, size(collect(DISTINCT sAmb)) AS nSites // collect() drops nulls, so an ambiguous or unmatched number falls out here. WITH collect(CASE WHEN nSites = ? THEN n END) AS siteNums MATCH (d:Document {studyId: $studyId}) // coalesce(), not bare `= false` like DocumentFetcher.fetch: this list must not // silently drop a document that lacks the archived property entirely. WHERE coalesce(d.delpending, false) = false AND coalesce(d.archived, false) = false AND ( d.siteId = $siteId OR ( (d.siteId IS NULL OR d.siteId = ?) AND trim(split(coalesce(d.fileName, ?), ?)[?]) <> ? AND trim(split(coalesce(d.fileName, ?), ?)[?]) IN siteNums ) ) OPTIONAL MATCH (d)-[:FILED_UNDER_SUBARTIFACT]->(saT:SubArtifact) OPTIONAL MATCH (d)<-[:RECOMMENDATION_TO_DOCUMENT]-(saR:Recommendation) WHERE saR.category = ? AND coalesce(saR.delpending, false) = false WITH d, saT, saR ORDER BY coalesce(saR.createdat, ?) DESC WITH d, saT, head(collect(saR)) AS lastSaR WITH d, coalesce(saT.name, lastSaR.data) AS subArtifactName WHERE subArtifactName IN $doaNames // The metadata agent?Document Metadata??' WITH d, subArtifactName, md ORDER BY coalesce(md.createdat, ?) DESC WITH d, subArtifactName, head(collect(md)) AS lastMd RETURN d.tildaid AS docId, subArtifactName AS title, d.fileName AS fileName, d.primaryVersionId AS primaryVersionId, lastMd.data_formatted AS metadataJson, d.cksum AS cksum, coalesce(d.createdat, ?) AS createdAt
write ontologysvc
2 148 ms 470 ms 280 ms flat
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_…
call { match (task:Task{assignment:?}) where (task.state = ? or task.state = ?) return task } with task where not exists ( (task)-[:CREATED_FROM_SPEC]-(:ParticipantTaskConfig)-[:PARTICIPANT_TASKCONFIG]-(:ParticipantTaskList)-[:PARTICIPANT_TASKLIST]-(:ParticipantControl) ) and not exists ( (task)<-[:CREATED_FROM_SPEC]-(:ParticipantDslSpecNode)<-[:PARTICIPANT_DSLTASK_TO_ALL_PARTICIPANT_DSLSPECNODE]-(:ParticipantDslTask)<-[:PARTICIPANT_DSLTASK]-(:ParticipantControl) ) match (task)-[:WITH_CONTEXT]->() optional match (task)-[:BELONGS_TO]->(userinbox:Inbox{type:?})-[:OWNED_BY]->(user:User) optional match (user)-[:USER_TO_PATIENT]-(patient:Patient) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(org:Organization) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(study:Study) optional match (task)-[:BELONGS_TO]->(:Inbox{type:?})-[:MANAGED_BY]->(site:Site) return distinct task, userinbox, org, study.name, site.name, patient.tildaid skip $skip limit $limit
tusker
10 170 ms 462 ms 323 ms flat
match (pd:DslPlanDefinition{tildaid: $pdId}) match (org:Organization{tildaid: $orgId})-[:STUDY_TO_ORG]-(st:Study{tildaid: $studyId}) call (org,st,pd) { match (pd)-[:PLAN_DEFINITION_TO_ACTIVITY]->(av:ActivityV2 ) where av.deltimestamp = ? a…
match (pd:DslPlanDefinition{tildaid: $pdId}) match (org:Organization{tildaid: $orgId})-[:STUDY_TO_ORG]-(st:Study{tildaid: $studyId}) call (org,st,pd) { match (pd)-[:PLAN_DEFINITION_TO_ACTIVITY]->(av:ActivityV2 ) where av.deltimestamp = ? and av.activityType = $activityType return null as site, null as r, av union match (st)-[:PLAN_DEFINITION_TO_STUDY]-(sitepd:DslPlanDefinition)-[:PLAN_DEFINITION_TO_SITE]-(site:Site) match (sitepd)-[r:PLAN_DEFINITION_TO_ADOPTED_ACTIVITIES|PLAN_DEFINITION_TO_PUBLISHED_ACTIVITIES|PLAN_DEFINITION_TO_PARENT_PUBLISHED_ACTIVITIES]-(av:ActivityV2 ) where av.deltimestamp = ? and av.activityType = $activityType return site, r, av } optional match (av)-[:ALL_CHANGELOG]->(cl:ChangeLog) WITH av, site, r, cl ORDER BY cl.createdat asc WITH av, site, r, head(collect(cl)) as cl optional match (u:User{tildaid:cl.who}) with av, site, r, case when u is not null then u.name else head(av.userNames) end as user RETURN distinct av, user, collect({site:site, r:type(r)})
execsvc
5 87 ms 462 ms 216 ms flat
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor…
MATCH (u:User) WHERE u.tildaid IN $userIds AND u.status<>?OPTIONAL MATCH (u)-[:INTERNAL_DATA]-(ui:UserInternal) WITH u, COALESCE(ui.pinHash IS NOT NULL AND ui.pinHash <> ?, false) AS pinSet, COALESCE(ui.needs2Factor, false) AS needs2Factor WITH u, u{.*, pinSet: pinSet, needs2Factor: needs2Factor} as profile CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:ORGANIZATION_TO_ACTOR]-(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, a, r order by r.displayName WITH u, o, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, orgType: o.orgType, status: o.status, startDate: a.startDate, endDate: a.endDate, capabilities: u.capabilities, role: roles, displayRoles: displayRoles, logoDocumentId: o.logoDocumentId, theme: o.theme, logoURL: o.logoURL, pinCheckEnabled: o.pinCheckEnabled }) AS organizations } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false,orgId:$orgId})<-[:STUDY_TO_ACTOR]-(s:Study{delpending:false}) WITH DISTINCT s, a, u.capabilities AS caps CALL { WITH a OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH r order by r.displayName RETURN apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles } MATCH (s)-[:STUDY_TO_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) // DISTINCT restores the dedup the old (u,o,s,a) grouping gave for free: two parallel // STUDY_TO_ORG edges would otherwise emit the study twice. The ORDER BY makes the // studies array a stable sequence for clients rather than a plan-dependent one. WITH DISTINCT s, a, caps, roles, displayRoles, o ORDER BY s.name, s.tildaid, o.tildaid RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, studyName: s.name, studySponsor: s.sponsor, studyId: s.tildaid, startDate: a.startDate, endDate: a.endDate, capabilities: caps, iSBlinded: a.isBlinded, role: roles, displayRoles: displayRoles }) AS studies } CALL { WITH u MATCH (u)<-[:FOR_USER]-(a:Actor{delpending:false})<-[:STAFF_TO_ACTOR]-(si:Site{delpending:false})-[:SITE_TO_SMO]->(:Smo)-[:IS_AN_ORG]->(o:Organization{tildaid:$orgId, delpending:false}) OPTIONAL MATCH (a)-[:APPOINTED_AS]->(r:Role) WITH u, o, si, a, r order by r.displayName WITH u, o, si, a, apoc.text.join(collect(distinct r.name), ?) as roles, [r IN collect(DISTINCT r) WHERE r.listed = true | r.displayName] AS displayRoles RETURN collect({ orgName: o.name, orgAlias: o.alias, orgId: o.tildaid, siteId: si.tildaid, siteName: si.name, siteCity: si.city, contactPhone: si.contactPhone, contactEmail: si.contactEmail, defaultLanguage: si.defaultLanguage, role: roles, displayRoles: displayRoles }) as sites } return profile{.*, organizations: organizations, studies: studies, sites: sites}
schedsvc
5 133 ms 462 ms 325 ms flat
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
MATCH (d:Document)-[:DOCUMENT_VERSION]->(ver:DocumentVersion {status: $pending}) WHERE ver.createdat <= $cutoff RETURN d.tildaid, ver.tildaid ORDER BY ver.createdat LIMIT $limit
docsvc
13 178 ms 461 ms 455 ms flat
match(org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_FORM]->(form:FormV2{type:$formType}) where (true) and (form.activityType is null or not form.activityType in $excludeActivityTypes) and not coalesce(form.tag, ?) in $systemFormTags …
match(org:Organization{tildaid:$orgId})-[:ORGANIZATION_TO_FORM]->(form:FormV2{type:$formType}) where (true) and (form.activityType is null or not form.activityType in $excludeActivityTypes) and not coalesce(form.tag, ?) in $systemFormTags and not form.state=? with org, count(form) as allCount optional match(org)-[:ORGANIZATION_TO_FORM]->(form:FormV2{type:$formType, state:?}) with org, allCount, count(form) as publishedCount optional match(org)-[:ORGANIZATION_TO_FORM]->(form:FormV2{type:$formType, state:?}) with org, allCount, publishedCount, count(form) as draftCount optional match(org)-[:ORGANIZATION_TO_FORM]->(form:FormV2{type:$formType, state:?}) return allCount, publishedCount, draftCount, count(form) as archivedCount
studiosvc
6 206 ms 455 ms 332 ms flat
MATCH (doc:Document {studyId: $studyId, ownerOrgId: $orgId}) WHERE doc.archived = false AND doc.status <> ? AND ($siteFilter IS NULL OR doc.fileName CONTAINS $siteFilter) OPTIONAL MATCH (doc)-[:FILED_UNDER_SUBARTIFACT]->(saT:SubArtifact) <…
MATCH (doc:Document {studyId: $studyId, ownerOrgId: $orgId}) WHERE doc.archived = false AND doc.status <> ? AND ($siteFilter IS NULL OR doc.fileName CONTAINS $siteFilter) OPTIONAL MATCH (doc)-[:FILED_UNDER_SUBARTIFACT]->(saT:SubArtifact) <-[:INCLUDES]-(f3T:Folder {level: ?}) <-[:INCLUDES]-(f2T:Folder {level: ?}) <-[:INCLUDES]-(f1T:Folder {level: ?}) OPTIONAL MATCH (doc)<-[:RECOMMENDATION_TO_DOCUMENT]-(saR:Recommendation {category: ?}) WITH doc, saT, f1T, f2T, f3T, saR ORDER BY saR.createdat DESC WITH doc, saT, f1T, f2T, f3T, collect(saR)[?] AS lastSaR OPTIONAL MATCH (doc)<-[:RECOMMENDATION_TO_DOCUMENT]-(arR:Recommendation {category: ?}) WITH doc, saT, f1T, f2T, f3T, lastSaR, arR ORDER BY arR.createdat DESC WITH doc, saT, f1T, f2T, f3T, lastSaR, collect(arR)[?] AS lastArR // Compute the canonical artifactId server-side so the zone/section/artifact // filters (and pagination) apply to the MATCHED set. TMF-folder path wins; // recommendation fallback parses ?. Zero-pad to ? digits. WITH doc, saT, f1T, f2T, f3T, lastSaR, lastArR, CASE WHEN f1T IS NOT NULL AND f2T IS NOT NULL AND f3T IS NOT NULL AND f1T.number IS NOT NULL AND f2T.number IS NOT NULL AND f3T.number IS NOT NULL THEN right(? + toString(f1T.number), ?) + ? + right(? + toString(f2T.number), ?) + ? + right(? + toString(f3T.number), ?) WHEN lastArR.data IS NOT NULL AND lastArR.data CONTAINS ? THEN split(lastArR.data, ?)[?] ELSE null END AS artifactId WHERE artifactId IS NOT NULL AND ($zone IS NULL OR left(artifactId, ?) = $zone) AND ($section IS NULL OR substring(artifactId, ?, ?) = $section) AND ($artifact IS NULL OR artifactId = $artifact) AND ($subartifact IS NULL OR coalesce(saT.name, lastSaR.data) = $subartifact) WITH doc, artifactId, saT, lastSaR WHERE doc.tmfLevel = ? AND toLower(trim(coalesce(doc.country,?))) = $country AND coalesce(saT.name, lastSaR.data) IN $names RETURN doc.tildaid AS docId, doc.updatedat AS updatedat, doc.primaryVersionId AS pvid, doc.fileName AS fn, doc.status AS status, doc.pages AS pages ORDER BY doc.tildaid
write ontologysvc
3 110 ms 455 ms 253 ms flat

p95 drift compares the query's p95 in the recent half of the window to the earlier half — ≥1.2× means it's slowing down. Click a row for its p50/p95 latency trend over time. Latency percentiles come from per-bucket histograms (no individual queries stored).