Dong Shin 11.23.2011

  • more PA changes
    • queries done, tested
    • changed BudgetCenter and created BudgetCenterDetail classes to map the new queries to AS objects
    • overhauling Financial Data Navigator to use new data types
    • realized that there could be more than one overdue/incomplete columns in a year, tweaked the query (longer!).
      • SELECT
        IF ( (((SUM(IF(ISNULL(month_1), 1, 0)) = 6 OR SUM(IF(ISNULL(month_2), 1, 0)) = 6) AND o.year+year_count=2013) OR ((SUM(IF(ISNULL(month_1), 1, 0)) = 6 OR SUM(IF(ISNULL(month_2), 1, 0)) = 6 OR SUM(IF(ISNULL(month_3), 1, 0)) = 6 OR SUM(IF(ISNULL(month_4), 1, 0)) = 6 OR SUM(IF(ISNULL(month_5), 1, 0)) = 6 OR SUM(IF(ISNULL(month_6), 1, 0)) = 6 OR SUM(IF(ISNULL(month_7), 1, 0)) = 6 OR SUM(IF(ISNULL(month_8), 1, 0)) = 6 OR SUM(IF(ISNULL(month_9), 1, 0)) = 6 OR SUM(IF(ISNULL(month_10), 1, 0)) = 6 OR SUM(IF(ISNULL(month_11), 1, 0)) = 6 OR SUM(IF(ISNULL(month_12), 1, 0)) = 6) AND o.year+year_count<2013)) ,’OVERDUE’,  ‘CURRENT’) as status,
        (
        IF ((SUM(IF(ISNULL(month_1), 1, 0)) = 6 AND o.year+year_count=2013), 1, 0) +
        IF ((SUM(IF(ISNULL(month_2), 1, 0)) = 6 AND o.year+year_count=2013), 1, 0) +
        IF ((SUM(IF(ISNULL(month_1), 1, 0)) = 6 AND o.year+year_count<2013), 1, 0) +
        IF ((SUM(IF(ISNULL(month_2), 1, 0)) = 6 AND o.year+year_count<2013), 1, 0) +
        IF ((SUM(IF(ISNULL(month_3), 1, 0)) = 6 AND o.year+year_count<2013), 1, 0) +
        IF ((SUM(IF(ISNULL(month_4), 1, 0)) = 6 AND o.year+year_count<2013), 1, 0) +
        IF ((SUM(IF(ISNULL(month_5), 1, 0)) = 6 AND o.year+year_count<2013), 1, 0) +
        IF ((SUM(IF(ISNULL(month_6), 1, 0)) = 6 AND o.year+year_count<2013), 1, 0) +
        IF ((SUM(IF(ISNULL(month_7), 1, 0)) = 6 AND o.year+year_count<2013), 1, 0) +
        IF ((SUM(IF(ISNULL(month_8), 1, 0)) = 6 AND o.year+year_count<2013), 1, 0) +
        IF ((SUM(IF(ISNULL(month_9), 1, 0)) = 6 AND o.year+year_count<2013), 1, 0) +
        IF ((SUM(IF(ISNULL(month_10), 1, 0)) = 6 AND o.year+year_count<2013), 1, 0) +
        IF ((SUM(IF(ISNULL(month_11), 1, 0)) = 6 AND o.year+year_count<2013), 1, 0) +
        IF ((SUM(IF(ISNULL(month_12), 1, 0)) = 6 AND o.year+year_count<2013), 1, 0)
        )
        as overdueCount,
        (
        IF ((SUM(IF(ISNULL(month_1), 1, 0)) > 0 AND SUM(IF(ISNULL(month_1), 1, 0)) < 6 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)) < 6  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)) < 6 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)) < 6 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)) < 6 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)) < 6 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)) < 6 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)) < 6 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)) < 6 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)) < 6 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)) < 6 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)) < 6 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)) < 6 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)) < 6 AND o.year+year_count<2013), 1, 0)
        )
        as incompleteCount,
        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 = 100 AND c.uid = o.funding_id AND a.budget_center_id = c.uid AND a.year = year_count AND c.appropriation = p.type GROUP BY o.year, year_count