Dong Shin 11.25.2011

  • created ItemRenderer and skin for Month Status ComboBox
  • realized that Reported Outlay and Outlay $ (Reported in FACTS) are not shown to Project Managers. These should not be counted in Project Status
  • new Query
    • SELECT
      IF ( (((SUM(IF(ISNULL(month_1), 1, 0)) = 4 OR SUM(IF(ISNULL(month_2), 1, 0)) = 4) AND o.year+year_count=2013) OR ((SUM(IF(ISNULL(month_1), 1, 0)) = 4 OR SUM(IF(ISNULL(month_2), 1, 0)) = 4 OR SUM(IF(ISNULL(month_3), 1, 0)) = 4 OR SUM(IF(ISNULL(month_4), 1, 0)) = 4 OR SUM(IF(ISNULL(month_5), 1, 0)) = 4 OR SUM(IF(ISNULL(month_6), 1, 0)) = 4 OR SUM(IF(ISNULL(month_7), 1, 0)) = 4 OR SUM(IF(ISNULL(month_8), 1, 0)) = 4 OR SUM(IF(ISNULL(month_9), 1, 0)) = 4 OR SUM(IF(ISNULL(month_10), 1, 0)) = 4 OR SUM(IF(ISNULL(month_11), 1, 0)) = 4 OR SUM(IF(ISNULL(month_12), 1, 0)) = 4) AND o.year+year_count<2013)) ,’OVERDUE’,  ‘CURRENT’) as status,
      (
      IF ((SUM(IF(ISNULL(month_1), 1, 0)) = 4 AND o.year+year_count=2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_2), 1, 0)) = 4 AND o.year+year_count=2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_1), 1, 0)) = 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_2), 1, 0)) = 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_3), 1, 0)) = 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_4), 1, 0)) = 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_5), 1, 0)) = 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_6), 1, 0)) = 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_7), 1, 0)) = 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_8), 1, 0)) = 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_9), 1, 0)) = 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_10), 1, 0)) = 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_11), 1, 0)) = 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_12), 1, 0)) = 4 AND o.year+year_count<2013), 1, 0)
      )
      as overdue_count,
      (
      IF ((SUM(IF(ISNULL(month_1), 1, 0)) > 0 AND SUM(IF(ISNULL(month_1), 1, 0)) < 4 AND o.year+year_count=2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_2), 1, 0)) > 0 AND SUM(IF(ISNULL(month_2), 1, 0)) < 4  AND o.year+year_count=2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_1), 1, 0)) > 0 AND SUM(IF(ISNULL(month_1), 1, 0)) < 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_2), 1, 0)) > 0 AND SUM(IF(ISNULL(month_2), 1, 0)) < 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_3), 1, 0)) > 0 AND SUM(IF(ISNULL(month_3), 1, 0)) < 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_4), 1, 0)) > 0 AND SUM(IF(ISNULL(month_4), 1, 0)) < 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_5), 1, 0)) > 0 AND SUM(IF(ISNULL(month_5), 1, 0)) < 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_6), 1, 0)) > 0 AND SUM(IF(ISNULL(month_6), 1, 0)) < 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_7), 1, 0)) > 0 AND SUM(IF(ISNULL(month_7), 1, 0)) < 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_8), 1, 0)) > 0 AND SUM(IF(ISNULL(month_8), 1, 0)) < 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_9), 1, 0)) > 0 AND SUM(IF(ISNULL(month_9), 1, 0)) < 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_10), 1, 0)) > 0 AND SUM(IF(ISNULL(month_10), 1, 0)) < 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_11), 1, 0)) > 0 AND SUM(IF(ISNULL(month_11), 1, 0)) < 4 AND o.year+year_count<2013), 1, 0) +
      IF ((SUM(IF(ISNULL(month_12), 1, 0)) > 0 AND SUM(IF(ISNULL(month_12), 1, 0)) < 4 AND o.year+year_count<2013), 1, 0)
      )
      as incomplete_count,
      c.*, a.uid as a_uid, a.amount as amount, o.year, o.year_count,  p.duration
      FROM budget_centers c, obligations_outlays o, budget_amounts a, appropriations p
      WHERE o.project_id = 173 AND c.uid = o.funding_id AND a.budget_center_id = c.uid AND a.year = year_count AND c.appropriation = p.type
      AND o.type <> ‘Reported Outlay $:’ AND o.type <> ‘Outlay $ (Reported in FACTS)’
      GROUP BY o.year, year_count