Dong Shin 05.29.2013

  • continue working on FA/RA
    • few bugs from today
      • inconsistent display of parent/children projects list in Project Management fixed
      • parent/child connection is broken, child/parent looks fine fixed
      • Total Project Budget datagrid shrinks (scrollbar) after saving project
      • SQL error when saving projects with sub projects
      • Totals for parent/sub projects not adding up correctly
    • getting Projects View to work with the new database structure….
    • working on Status View – done!
    • working on Line Item View
    • query to get status for projects requisition data
      • SELECT p.center_name, p.center_number, p.capability, p.appropriation, p.type, c.*, 0 as a_uid, c.total_budget as amount, o.year, o.year_count, r.duration, o.uid as o_uid,
        IF (c.total_budget = 0 OR o.year + o.year_count – 1 > 2013, ‘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 (c.total_budget = 0 OR o.year + o.year_count – 1 > 2013, ‘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 (c.total_budget = 0 OR o.year + o.year_count – 1 > 2013, ‘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 (c.total_budget = 0 OR o.year + o.year_count – 1 > 2013, ‘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 (c.total_budget = 0 OR o.year + o.year_count – 1 > 2013, ‘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 (c.total_budget = 0 OR o.year + o.year_count – 1 > 2013, ‘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 (c.total_budget = 0 OR o.year + o.year_count – 1 > 2013, ‘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 (c.total_budget = 0 OR o.year + o.year_count – 1 > 2013, ‘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 (c.total_budget = 0 OR o.year + o.year_count – 1 > 2013, ‘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 (c.total_budget = 0 OR o.year + o.year_count – 1 > 2013, ‘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 (c.total_budget = 0 OR o.year + o.year_count – 1 > 2013, ‘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 (c.total_budget = 0 OR o.year + o.year_count – 1 > 2013, ‘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,
        0 as funded_budget,
        0 as funded_budget_flag,
        ‘contract number’ as contracts
        FROM budget_centers c, obligations_outlays o, appropriations r, projects p
        WHERE p.uid = 3 AND c.project_id = p.uid AND c.uid = o.funding_id AND p.appropriation = r.type
        AND o.type <> ‘Reported Outlay $:’ AND o.type <> ‘Outlay $ (Reported in FACTS)’
        GROUP BY c.uid, o.year, o.year_count
        ORDER BY c.uid, o.year, o.year_count