# roger 10 dangerous[~24] / 2004-03-15 # mike 10 rmanagement[~24] # mike 10 ssite[~24] # joe 10 ssite[~24] # roger 10 ssite[~24] # mike 10 tmotivation[~24] # mike 10 wusage[~24] # roger 10 wusage[~24] # joe 10 wusage[~24] # # roger 5 engine[~24] # roger 5 fiber[~24] / 2003-03-03 # joe 5 fiber[~24] / 2003-03-03 # mike 5 fiber[~24] / 2002-02-02 # roger 5 fproblems[] / 2006-03-20 # roger 5 tinformation[~12] / 2002-05-05 # mike 5 vision[~24] / 2003-04-05 # # joe 1 fproblems[] # mike 1 fproblems[] # roger 1 radio[~24] # joe 1 radio[~24] # mike 1 radio[~24] # mike 1 tinformation[~12] # joe 1 tinformation[~12] / 2003-03-20 # # =============== # # joe 10 ssite[~24] # joe 10 wusage[~24] # joe 5 fiber[~24] / 2003-03-03 # joe 1 fproblems[] # joe 1 radio[~24] # joe 1 tinformation[~12] / 2003-03-20 # # mike 10 rmanagement[~24] # mike 10 ssite[~24] # mike 10 tmotivation[~24] # mike 10 wusage[~24] # mike 5 fiber[~24] / 2002-02-02 # mike 5 vision[~24] / 2003-04-05 # mike 1 fproblems[] # mike 1 radio[~24] # mike 1 tinformation[~12] # # roger 10 dangerous[~24] / 2004-03-15 # roger 10 ssite[~24] # roger 10 wusage[~24] # roger 5 engine[~24] # roger 5 fiber[~24] / 2003-03-03 # roger 5 fproblems[] / 2006-03-20 # roger 5 tinformation[~12] / 2002-05-05 # roger 1 radio[~24] # FIXME: replace all these with simpler tests. Node: # Used to test custom select types (repeat_every, last_date) emp_form_date: # find courses assigned_to pages in job assigned_to CURRENT_NODE (employee) main_table: courses select: - courses.* - courses.custom_a AS repeat_every - form.last_date AS last_date - form.last_date + INTERVAL courses.custom_a MONTH AS next_date - MAX(IF(assigned_pages.custom_a,assigned_pages.custom_a,5)) AS priority tables: - nodes AS courses # assigned to - links AS li1 # pages - nodes AS assigned_pages # in assigned_jobs - links join_tables: # courses courses: # all formations are created inside the course = project. This is why # form.project_id == course_id. - LEFT JOIN (SELECT nodes.project_id AS course_id, flink.target_id AS emp_id, MAX(nodes.event_at) AS last_date FROM nodes INNER JOIN links AS flink ON nodes.id = flink.source_id AND flink.relation_id = RELATION_ID(assigned_formation) AND (flink.status IS NULL OR flink.status = 100) GROUP BY flink.target_id, nodes.project_id) AS form ON courses.id = form.course_id AND form.emp_id = NODE_ID - INNER JOIN idx_nodes_ml_strings AS _ml1 ON courses.id = _ml1.node_id AND _ml1.key = 'title' AND _ml1.lang = VISITOR_LANG where: # get all assigned jobs (links) - links.source_id = NODE_ID - links.relation_id = RELATION_ID(assigned_job) # get all pages in jobs - assigned_pages.project_id = links.target_id # get all courses assigned to these pages - li1.target_id = assigned_pages.id - li1.relation_id = RELATION_ID(assigned_course) - courses.id = li1.source_id group: courses.id order: priority DESC, last_date ASC, _ml1.value ASC secure_table: # Stupid query to test SECURE_TABLE main_table: emp select: - other.* tables: - nodes AS emp - nodes AS other where: - SECURE_TABLE(other) group: other.id order: other.zip ASC course_emp_date: main_table: employees select: - employees.id - employees.publish_from - employees.zip - employees.vclass_id - employees.type - employees.wgroup_id - employees.dgroup_id - employees.ref_lang - employees.vhash - employees.user_id # TODO: next_date = NULL if custom_a is null - form.last_date AS last_date - form.last_date + INTERVAL NODE_ATTR(custom_a) MONTH AS next_date - NODE_ATTR(custom_a) AS repeat_every - MAX(IF(assigned_pages.custom_a,assigned_pages.custom_a,5)) AS priority tables: # NODE_ID = course - nodes AS employees - nodes AS assigned_pages - links - links AS li1 join_tables: employees: - LEFT JOIN (SELECT nodes.project_id AS project_id, flink.target_id AS emp_id, MAX(nodes.event_at) AS last_date FROM nodes INNER JOIN links AS flink ON nodes.id = flink.source_id AND flink.relation_id = RELATION_ID(assigned_formation) AND (flink.status IS NULL OR flink.status = 100) GROUP BY flink.target_id, nodes.project_id) AS form ON form.emp_id = employees.id AND form.project_id = NODE_ID - INNER JOIN idx_nodes_ml_strings AS _ml1 ON employees.id = _ml1.node_id AND _ml1.key = 'title' AND _ml1.lang = VISITOR_LANG where: - employees.id = li1.source_id AND li1.relation_id = RELATION_ID(assigned_employee) AND li1.target_id = assigned_pages.project_id - assigned_pages.id = links.target_id AND links.relation_id = RELATION_ID(assigned_page) AND links.source_id = NODE_ID group: employees.id order: _ml1.value ASC, IF(form.last_date,form.last_date,0) ASC all_course_emp_date: # assigned_employees from project from assigned_pages from courses in site # all courses that need to have a formation created main_table: courses select: - courses.id - courses.publish_from - courses.zip - courses.vclass_id - courses.type - courses.wgroup_id - courses.dgroup_id - courses.ref_lang - courses.vhash - courses.user_id - courses.custom_a AS repeat_every - courses.priority - form_status.last_date AS last_date - form_status.last_date + INTERVAL courses.custom_a MONTH AS next_date - MIN(IF(form_status.last_date,form_status.last_date,0)) AS min_last_date - MIN(IF(form_status.last_date,form_status.last_date,0)) + INTERVAL courses.custom_a MONTH AS min_next_date - COUNT(DISTINCT courses.employee_id) AS emp_count tables: # course - max(priority) - employee - "(SELECT courses.*, employees.id AS employee_id, MAX(IF(assigned_pages.custom_a,assigned_pages.custom_a,5)) AS priority FROM nodes AS courses, nodes AS employees, nodes AS assigned_pages, links AS li1, links AS li2 WHERE (employees.id = li1.source_id AND li1.relation_id = RELATION_ID(assigned_employee) AND li1.target_id = assigned_pages.project_id) AND (assigned_pages.id = li2.target_id AND li2.relation_id = RELATION_ID(assigned_page) AND li2.source_id = courses.id) GROUP BY id, employee_id) AS courses" join_tables: courses: - "LEFT JOIN (SELECT formations.project_id AS course_id, flink.target_id AS employee_id, MAX(formations.event_at) AS last_date FROM nodes AS formations INNER JOIN links AS flink ON formations.id = flink.source_id AND flink.relation_id = RELATION_ID(assigned_formation) AND (flink.status IS NULL OR flink.status = 100) GROUP BY employee_id, course_id) AS form_status ON form_status.employee_id = courses.employee_id AND form_status.course_id = courses.id" where: - courses.kpath LIKE 'NPPC%' group: courses.priority, courses.id order: min_next_date ASC all_formations_not_over: main_table: formations select: - formations.* tables: - nodes AS formations join_tables: formations: - "INNER JOIN links ON formations.id = links.source_id AND links.relation_id = RELATION_ID(assigned_formation) AND (links.status IS NULL OR links.status = 50)" where: - formations.kpath like 'NNF%' group: formations.id alias_query: main_table: nodes select: - "(log_at + INTERVAL 6 months) AS event_at" tables: - nodes