-- TimelineActivity


-- Delayed
SELECT 
  u0_.id AS id0, 
  u1_.firstname AS firstname1, 
  u1_.id AS id2, 
  u1_.host_type AS host_type3, 
  u2_.name AS name4, 
  u3_.title AS title5,
  u0_.start_date,
  u0_.end_date,
  u0_.status

  
FROM 
  us_timeline_timeline_activity u0_ 
  LEFT JOIN us_timeline_timeline u4_ ON u0_.timeline_id = u4_.id 
  LEFT JOIN us_activity_activity u3_ ON u0_.activity_id = u3_.id 
  LEFT JOIN us_activity_survey u5_ ON u3_.id = u5_.id 
  LEFT JOIN us_activity_test u6_ ON u3_.id = u6_.id 
  LEFT JOIN us_activity_form u7_ ON u3_.id = u7_.id 
  LEFT JOIN us_activity_tasklist u8_ ON u3_.id = u8_.id 
  LEFT JOIN us_activity_media u9_ ON u3_.id = u9_.id 
  LEFT JOIN us_activity_dialog u10_ ON u3_.id = u10_.id 
  LEFT JOIN us_game_game u11_ ON u3_.id = u11_.id 
  LEFT JOIN us_activity_satisfaction_survey u12_ ON u3_.id = u12_.id 
  LEFT JOIN us_user_user u1_ ON u4_.user_id = u1_.id 
  LEFT JOIN us_user_user u13_ ON u4_.responsible_id = u13_.id 
  LEFT JOIN us_timeline_timeline_phase u14_ ON u4_.id = u14_.timeline_id 
  LEFT JOIN us_timeline_phase u2_ ON u14_.phase_id = u2_.id 
WHERE 
  u0_.status = 4 
ORDER BY 
  u3_.title ASC;
  
  
SELECT 
  u0_.name AS name0, 
  (
    SELECT 
      count(u1_.id) AS dctrn__1 
    FROM 
      us_timeline_timeline u1_ 
      LEFT JOIN us_user_user u2_ ON u1_.user_id = u2_.id 
      LEFT JOIN us_user_direction u3_ ON u2_.direction_id = u3_.id 
    WHERE 
      u1_.status = 2 
      AND u1_.user_id = u2_.id 
      AND u3_.id = u0_.id
  ) AS sclr1, 
  (
    SELECT 
      count(u4_.id) AS dctrn__2 
    FROM 
      us_timeline_timeline u4_ 
      LEFT JOIN us_user_user u5_ ON u4_.user_id = u5_.id 
      LEFT JOIN us_user_direction u6_ ON u5_.direction_id = u6_.id 
    WHERE 
      u4_.status = 1 
      AND u4_.user_id = u5_.id 
      AND u6_.id = u0_.id
  ) AS sclr2, 
  (
    SELECT 
      count(u7_.id) AS dctrn__3 
    FROM 
      us_timeline_timeline u7_ 
      LEFT JOIN us_user_user u8_ ON u7_.user_id = u8_.id 
      LEFT JOIN us_user_direction u9_ ON u8_.direction_id = u9_.id 
    WHERE 
      u7_.user_id = u8_.id 
      AND u9_.id = u0_.id
  ) AS sclr3 
FROM 
  us_user_direction u0_ 
  LEFT JOIN us_user_user u10_ ON u0_.id = u10_.direction_id 
GROUP BY 
  u0_.name;  
  
  
  SELECT 
  u0_.name AS name0, 
  (
    SELECT 
      count(u1_.id) AS dctrn__1 
    FROM 
      us_timeline_timeline u1_ 
      LEFT JOIN us_user_user u2_ ON u1_.user_id = u2_.id 
      LEFT JOIN us_user_direction u3_ ON u2_.direction_id = u3_.id 
    WHERE 
      u1_.user_id = u2_.id 
      AND u3_.id = u0_.id 
      AND u1_.status = 2
  ) AS sclr1, 
  (
    SELECT 
      count(u4_.id) AS dctrn__2 
    FROM 
      us_timeline_timeline u4_ 
      LEFT JOIN us_user_user u5_ ON u4_.user_id = u5_.id 
      LEFT JOIN us_user_direction u6_ ON u5_.direction_id = u6_.id 
    WHERE 
      u4_.user_id = u5_.id 
      AND u6_.id = u0_.id 
      AND u4_.status = 1
  ) AS sclr2, 
  (
    SELECT 
      count(u7_.id) AS dctrn__3 
    FROM 
      us_timeline_timeline u7_ 
      LEFT JOIN us_user_user u8_ ON u7_.user_id = u8_.id 
      LEFT JOIN us_user_direction u9_ ON u8_.direction_id = u9_.id 
    WHERE 
      u7_.user_id = u8_.id 
      AND u9_.id = u0_.id
  ) AS sclr3 
FROM 
  us_user_direction u0_ 
  LEFT JOIN us_user_user u10_ ON u0_.id = u10_.direction_id 
GROUP BY 
  u0_.name;
  
-- IncompleteRange 
SELECT 
  u0_.id AS id0, 
  u0_.title AS title1, 
  u0_.description AS description2, 
  u0_.is_layout AS is_layout3, 
  u0_.status AS status4, 
  u0_.start_date AS start_date5, 
  u0_.end_date AS end_date6, 
  u0_.createdAt AS createdAt7, 
  u0_.updatedAt AS updatedAt8, 
  (
    SELECT 
      count(u1_.id) AS dctrn__1 
    FROM 
      us_timeline_timeline_activity u1_ 
    WHERE 
      u1_.timeline_id = u0_.id 
      AND u1_.status = 2
  ) AS sclr9, 
  (
    SELECT 
      count(u2_.id) AS dctrn__2 
    FROM 
      us_timeline_timeline_activity u2_ 
    WHERE 
      u2_.timeline_id = u0_.id
  ) AS sclr10, 
  u0_.user_id AS user_id11, 
  u0_.responsible_id AS responsible_id12, 
  u0_.mentor_id AS mentor_id13 
FROM 
  us_timeline_timeline u0_ 
  LEFT JOIN us_timeline_timeline_activity u3_ ON u0_.id = u3_.timeline_id 
  LEFT JOIN us_user_user u4_ ON u0_.user_id = u4_.id 
GROUP BY 
  u0_.id;  
  
-- IncompleteRange by direction
SELECT 
  u0_.name AS name0, 
  u1_.firstname AS firstname1, 
  u1_.lastname AS lastname2, 
  (
    SELECT 
      count(u2_.id) AS dctrn__1 
    FROM 
      us_timeline_timeline_activity u2_ 
    WHERE 
      u2_.timeline_id = u3_.id 
      AND u2_.status = 2
  ) AS sclr3, 
  (
    SELECT 
      count(u4_.id) AS dctrn__2 
    FROM 
      us_timeline_timeline_activity u4_ 
    WHERE 
      u4_.timeline_id = u3_.id
  ) AS sclr4 
  
FROM 
  us_timeline_timeline u3_ 
  LEFT JOIN us_timeline_timeline_activity u5_ ON u3_.id = u5_.timeline_id 
  LEFT JOIN us_user_user u1_ ON u3_.user_id = u1_.id 
  LEFT JOIN us_user_direction u0_ ON u1_.direction_id = u0_.id
WHERE u1_.direction_id is not null
GROUP BY 
  u3_.id 
ORDER BY 
  u0_.name ASC;
  
  
-- Incomplete by hostType
SELECT 
  u0_.host_type AS host_type0, 
  u0_.firstname AS firstname1, 
  u0_.lastname AS lastname2, 
  (
    SELECT 
      count(u1_.id) AS dctrn__1 
    FROM 
      us_timeline_timeline_activity u1_ 
    WHERE 
      u1_.timeline_id = u2_.id 
      AND u1_.status = 2
  ) AS sclr3, 
  (
    SELECT 
      count(u3_.id) AS dctrn__2 
    FROM 
      us_timeline_timeline_activity u3_ 
    WHERE 
      u3_.timeline_id = u2_.id
  ) AS sclr4 
FROM 
  us_timeline_timeline u2_ 
  LEFT JOIN us_timeline_timeline_activity u4_ ON u2_.id = u4_.timeline_id 
  LEFT JOIN us_user_user u0_ ON u2_.user_id = u0_.id 

WHERE u2_.user_id is not null

GROUP BY 
  u2_.id 
ORDER BY 
  u0_.host_type ASC;
  
