------------------------------------------------
-- CompliancePlanController::byDirectionAction
------------------------------------------------

--
-- getCompliancePlanByDirection  [JOIN]
--
SELECT d.name , numTotal, numActive, numEnded FROM us_user_direction d LEFT JOIN (SELECT _d.name name, count(tl.id) AS numTotal FROM us_timeline_timeline tl LEFT JOIN us_user_user u ON u.id = tl.user_id LEFT JOIN us_user_direction _d ON _d.id = u.direction_id GROUP BY _d.name) numTotal ON numTotal.name = d.name LEFT JOIN (SELECT _d1.name name, count(tl.id) AS numActive FROM us_timeline_timeline tl LEFT JOIN us_user_user u ON u.id = tl.user_id LEFT JOIN us_user_direction _d1 ON _d1.id = u.direction_id WHERE tl.status = 1 GROUP BY _d1.name) numActive ON numActive.name = d.name LEFT JOIN (SELECT _d2.name name, count(tl.id) AS numEnded FROM us_timeline_timeline tl LEFT JOIN us_user_user u ON u.id = tl.user_id LEFT JOIN us_user_direction _d2 ON _d2.id = u.direction_id WHERE tl.status = 2 GROUP BY _d2.name) numEnded ON numEnded.name = d.name GROUP BY d.name;
    
SELECT ud.name,  numTotal, numActive, numEnded
FROM us_user_direction ud
LEFT JOIN (
    SELECT u0_.name AS name, count(u1_.id) AS numTotal 
    FROM us_timeline_timeline u1_ 
    LEFT JOIN us_user_user u2_ ON u1_.user_id = u2_.id 
    LEFT JOIN us_user_direction u0_ ON u2_.direction_id = u0_.id 
    GROUP BY u0_.name
    ) numTotal
    ON numTotal.name = ud.name 
 
 LEFT JOIN (    
    SELECT u00_.name AS name, count(u11_.id) AS numActive 
    FROM us_timeline_timeline u11_ 
    LEFT JOIN us_user_user u22_ ON u11_.user_id = u22_.id 
    LEFT JOIN us_user_direction u00_ ON u22_.direction_id = u00_.id 
    WHERE u11_.status = 1 
    GROUP BY u00_.name
) numActive
ON numTotal.name = numActive.name 

LEFT JOIN (
    SELECT u000_.name AS name, count(u111_.id) AS numEnded 
    FROM us_timeline_timeline u111_ 
    LEFT JOIN us_user_user u222_ ON u111_.user_id = u222_.id 
    LEFT JOIN us_user_direction u000_ ON u222_.direction_id = u000_.id 
    WHERE u111_.status = 2 
    GROUP BY u000_.name
) numEnded
ON numEnded.name = numActive.name 

GROUP BY ud.name;


--
-- getCompliancePlanByDirection  [MULTIPLE SELECTS]
--
SELECT u0_.id AS id0, u0_.name AS name1, 
  (
    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 sclr2, 
  (
    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 sclr3, 
  (
    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 sclr4, 
  u0_.name AS name5, 
  u0_.id AS id6 
FROM 
  us_user_direction u0_ 
  LEFT JOIN us_user_user u10_ ON u0_.id = u10_.direction_id 
GROUP BY 
  u0_.name; 
 

  


