Dong Shin 10.12.2011

  • PPM Widgets
    • added Project status
    • updated FinancialDataView to have the fields from Project, added FinancialDataViewStatus for project status
    • added query to retrieve the status of the project (may need fine tuning, takes about 4 seconds to retrieve the data)
    • burned the software to test on-site
    • working on Budget Centers, Years, Year Counts for overdue projects
      • select b.`uid` AS `uid`,
        b.`project_id` AS `project_id`,
        `appropriations`.`status` AS `status`,
        ‘complete’ as project_status,
        O_type, o_year, o_year_count, o_uid, o_month_1
        from `budget_centers` b
        left join `appropriations` on
        b.`appropriation` = `appropriations`.`type`
        left join __view_financial_data_view
        on project_id = p_uid and b.uid = c_uid
        where p_uid = 100 and o_year = 2012 and o_year_count = 1
        and ISNULL(o_month_1)
        and uid not in (
        select b.`uid` AS `uid`
        from `budget_centers` b
        left join `appropriations` on
        b.`appropriation` = `appropriations`.`type`
        left join __view_financial_data_view
        on project_id = p_uid and b.uid = c_uid
        where p_uid = 100 and o_year = 2012 and o_year_count = 1
        and NOT ISNULL(o_month_1)
        GROUP BY uid)
        GROUP BY uid
        UNION

        select b.`uid` AS `uid`,
        b.`project_id` AS `project_id`,
        `appropriations`.`status` AS `status`,
        ‘overdue’ as project_status,
        O_type, o_year, o_year_count, o_uid, o_month_1
        from `budget_centers` b
        left join `appropriations` on
        b.`appropriation` = `appropriations`.`type`
        left join __view_financial_data_view
        on project_id = p_uid and b.uid = c_uid
        where p_uid = 100 and o_year = 2012 and o_year_count = 1
        and NOT ISNULL(o_month_1)
        GROUP BY uid