Dong Shin 11.30.2011

  • Financial Data navigation in Project Assistant
    • takes to the beginning of the records when option changes
    • combined checkbox navigation – done
    • working on current months navigation – done coding
    • testing would take a while….
    • revised the Budget Details query to ignore $0 budgets and years beyond current year
      • SELECT c.*, a.uid as a_uid, a.amount as amount, o.year, o.year_count, r.duration,
        IF (amount = 0 OR o.year + o.year_count – 1 > 2012, ‘IGNORE’, (IF(SUM(IF(ISNULL(month_1), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_1), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)))) as month_1_status,
        IF (amount = 0 OR o.year + o.year_count – 1 > 2012, ‘IGNORE’, (IF(SUM(IF(ISNULL(month_2), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_2), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)))) as month_2_status,
        IF (amount = 0 OR o.year + o.year_count – 1 > 2012, ‘IGNORE’, (IF(SUM(IF(ISNULL(month_3), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_3), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)))) as month_3_status,
        IF (amount = 0 OR o.year + o.year_count – 1 > 2012, ‘IGNORE’, (IF(SUM(IF(ISNULL(month_4), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_4), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)))) as month_4_status,
        IF (amount = 0 OR o.year + o.year_count – 1 > 2012, ‘IGNORE’, (IF(SUM(IF(ISNULL(month_5), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_5), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)))) as month_5_status,
        IF (amount = 0 OR o.year + o.year_count – 1 > 2012, ‘IGNORE’, (IF(SUM(IF(ISNULL(month_6), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_6), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)))) as month_6_status,
        IF (amount = 0 OR o.year + o.year_count – 1 > 2012, ‘IGNORE’, (IF(SUM(IF(ISNULL(month_7), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_7), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)))) as month_7_status,
        IF (amount = 0 OR o.year + o.year_count – 1 > 2012, ‘IGNORE’, (IF(SUM(IF(ISNULL(month_8), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_8), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)))) as month_8_status,
        IF (amount = 0 OR o.year + o.year_count – 1 > 2012, ‘IGNORE’, (IF(SUM(IF(ISNULL(month_9), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_9), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)))) as month_9_status,
        IF (amount = 0 OR o.year + o.year_count – 1 > 2012, ‘IGNORE’, (IF(SUM(IF(ISNULL(month_10), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_10), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)))) as month_10_status,
        IF (amount = 0 OR o.year + o.year_count – 1 > 2012, ‘IGNORE’, (IF(SUM(IF(ISNULL(month_11), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_11), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)))) as month_11_status,
        IF (amount = 0 OR o.year + o.year_count – 1 > 2012, ‘IGNORE’, (IF(SUM(IF(ISNULL(month_12), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_12), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)))) as month_12_status
        FROM budget_centers c, obligations_outlays o, budget_amounts a, appropriations r, projects p
        WHERE p.uid = 174 AND c.project_id = p.uid AND c.uid = a.budget_center_id AND a.year = o.year – p.begin_year + 1 AND c.uid = o.funding_id AND c.appropriation = r.type
        AND o.type <> ‘Reported Outlay $:’ AND o.type <> ‘Outlay $ (Reported in FACTS)’ GROUP BY a.uid, o.year, o.year_count ORDER BY c.uid, o.year, o.year_count