Dong Shin 10.06.11

  • Meeting with Tangie at Fort and some enhancements requested
    • make fonts bigger
    • distinguishable header info in Financial Status Data. It should be each row of the project’s budget data and selectable/changeable
    • PM Actual Outlays link to the Financial Data
    • Possible meetings on Mon/Thu mornings
  • Continue working on PPM Widgets
    • basic monthly display is done
    • working on query to get whole financial data at one year at a time…Goals and data, $ and %, – all calculated.
      1. Obligations Goals %
      2. Obligations Goals $ – calculated from budget amount
      3. Obligation % and $ from Financial Data…. multiple data
      4. Outlay Goals %
      5. Outlay Goals $ – calculated from budget amount
      6. Outlay % and $ from Financial Data… multiple data
    • Goals Data query….for project uid 100
      • SELECT * FROM (
        SELECT
        p.uid as p_uid,
        c.uid as c_uid,
        a.uid as a_uid,
        a.amount as a_amount,
        null as o_uid,
        ‘Obligation %:’ as o_type,
        (p.begin_year + a.year – 1) as o_year,
        a.year as o_year_count,
        null as o_project_id,
        null as o_funding_id,
        CONCAT (g.obligation_month_1, ‘%’) as o_month_1
        FROM `projects` p,
        budget_centers c,
        budget_amounts a,
        obligations_outlays_goals g
        WHERE p.uid = c.project_id
        AND c.uid = a.budget_center_id
        AND c.appropriation = g.appropriation
        AND a.year = g.year
        UNION
        SELECT
        p.uid as p_uid,
        c.uid as c_uid,
        a.uid as a_uid,
        a.amount as a_amount,
        null as o_uid,
        ‘Obligation $:’ as o_type,
        (p.begin_year + a.year – 1) as o_year,
        a.year as o_year_count,
        null as o_project_id,
        null as o_funding_id,
        (a.amount * g.obligation_month_1 / 100) as o_month_1
        FROM `projects` p,
        budget_centers c,
        budget_amounts a,
        obligations_outlays_goals g
        WHERE p.uid = c.project_id
        AND c.uid = a.budget_center_id
        AND c.appropriation = g.appropriation
        AND a.year = g.year
        UNION
        SELECT
        p.uid as p_uid,
        c.uid as c_uid,
        a.uid as a_uid,
        a.amount as a_amount,
        o.uid as o_uid,
        o.type as o_type,
        o.year as o_year,
        o.year_count as o_year_count,
        o.project_id as o_project_id,
        o.funding_id as o_funding_id,
        o.month_1 as o_month_1
        FROM `projects` p,
        budget_centers c,
        budget_amounts a,
        obligations_outlays o,
        obligations_outlays_goals g
        WHERE p.uid = c.project_id
        AND c.uid = a.budget_center_id
        AND o.funding_id = c.uid
        AND a.year = o.year_count
        AND c.appropriation = g.appropriation
        AND a.year = g.year
        AND o.type LIKE ‘%Obligated%’
        UNION
        SELECT
        p.uid as p_uid,
        c.uid as c_uid,
        a.uid as a_uid,
        a.amount as a_amount,
        o.uid as o_uid,
        REPLACE (o.type, ‘$:’, ‘%:’) as o_type,
        o.year as o_year,
        o.year_count as o_year_count,
        o.project_id as o_project_id,
        o.funding_id as o_funding_id,
        (o.month_1 / a.amount) as o_month_1
        FROM `projects` p,
        budget_centers c,
        budget_amounts a,
        obligations_outlays o,
        obligations_outlays_goals g
        WHERE p.uid = c.project_id
        AND c.uid = a.budget_center_id
        AND o.funding_id = c.uid
        AND a.year = o.year_count
        AND c.appropriation = g.appropriation
        AND a.year = g.year
        AND o.type LIKE ‘%Obligated%’
        UNION
        SELECT
        p.uid as p_uid,
        c.uid as c_uid,
        a.uid as a_uid,
        a.amount as a_amount,
        null as o_uid,
        ‘Outlay %:’ as o_type,
        (p.begin_year + a.year – 1) as o_year,
        a.year as o_year_count,
        null as o_project_id,
        null as o_funding_id,
        CONCAT (g.outlay_month_1, ‘%’) as o_month_1
        FROM `projects` p,
        budget_centers c,
        budget_amounts a,
        obligations_outlays_goals g
        WHERE p.uid = c.project_id
        AND c.uid = a.budget_center_id
        AND c.appropriation = g.appropriation
        AND a.year = g.year
        UNION
        SELECT
        p.uid as p_uid,
        c.uid as c_uid,
        a.uid as a_uid,
        a.amount as a_amount,
        null as o_uid,
        ‘Outlay $:’ as o_type,
        (p.begin_year + a.year – 1) as o_year,
        a.year as o_year_count,
        null as o_project_id,
        null as o_funding_id,
        (a.amount * g.outlay_month_1 / 100) as o_month_1
        FROM `projects` p,
        budget_centers c,
        budget_amounts a,
        obligations_outlays_goals g
        WHERE p.uid = c.project_id
        AND c.uid = a.budget_center_id
        AND c.appropriation = g.appropriation
        AND a.year = g.year
        UNION
        SELECT
        p.uid as p_uid,
        c.uid as c_uid,
        a.uid as a_uid,
        a.amount as a_amount,
        o.uid as o_uid,
        o.type as o_type,
        o.year as o_year,
        o.year_count as o_year_count,
        o.project_id as o_project_id,
        o.funding_id as o_funding_id,
        o.month_1 as o_month_1
        FROM `projects` p,
        budget_centers c,
        budget_amounts a,
        obligations_outlays o,
        obligations_outlays_goals g
        WHERE p.uid = c.project_id
        AND c.uid = a.budget_center_id
        AND o.funding_id = c.uid
        AND a.year = o.year_count
        AND c.appropriation = g.appropriation
        AND a.year = g.year
        AND o.type LIKE ‘%Outlay%’
        UNION
        SELECT
        p.uid as p_uid,
        c.uid as c_uid,
        a.uid as a_uid,
        a.amount as a_amount,
        o.uid as o_uid,
        REPLACE (o.type, ‘$:’, ‘%:’) as o_type,
        o.year as o_year,
        o.year_count as o_year_count,
        o.project_id as o_project_id,
        o.funding_id as o_funding_id,
        (o.month_1 / a.amount) as o_month_1
        FROM `projects` p,
        budget_centers c,
        budget_amounts a,
        obligations_outlays o,
        obligations_outlays_goals g
        WHERE p.uid = c.project_id
        AND c.uid = a.budget_center_id
        AND o.funding_id = c.uid
        AND a.year = o.year_count
        AND c.appropriation = g.appropriation
        AND a.year = g.year
        AND o.type LIKE ‘%Outlay%’
        ) AS X
        WHERE p_uid = 100 AND o_year=2011 AND o_year_count=1