Dong Shin 08.28.2013

  • continue working on Query Builder (FA)
    • changed the way how Obligated/Outlay/PM Actual columns are handled. Had all the columns and set the visible property to false on all, but the one that’s selected. This seems confuses the FooterDatagrid as it showed all the footers in the bottom,….. Now one column for each, just change the dataField properties when the month selection happens.
    • need to delete unnecessary columns from obligations_outlays_goals
      • ALTER TABLE `obligations_outlays_goals` DROP `project_id`, DROP `funding_id`;
    • query to find obligations/outlays/pm actuals and goals/percents
      • SELECT project_number, req_description, req_id, projects.appropriation, budget_centers.total_budget,
        GROUP_CONCAT(o.uid),
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_1, 0)) AS obligated_month_1, SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_2, 0)) AS obligated_month_2,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_3, 0)) AS obligated_month_3, SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_4, 0)) AS obligated_month_4,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_5, 0)) AS obligated_month_5, SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_6, 0)) AS obligated_month_6,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_7, 0)) AS obligated_month_7, SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_8, 0)) AS obligated_month_8,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_9, 0)) AS obligated_month_9, SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_10, 0)) AS obligated_month_10,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_11, 0)) AS obligated_month_11, SUM(IF(o.type = ‘Reported FACTS Obligated $:’, month_12, 0)) AS obligated_month_12,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_1, 0)) AS outlay_month_1, SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_2, 0)) AS outlay_month_2,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_3, 0)) AS outlay_month_3, SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_4, 0)) AS outlay_month_4,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_5, 0)) AS outlay_month_5, SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_6, 0)) AS outlay_month_6,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_7, 0)) AS outlay_month_7, SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_8, 0)) AS outlay_month_8,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_9, 0)) AS outlay_month_9, SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_10, 0)) AS outlay_month_10,
        SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_1, 0)) AS pm_actuals_month_1, SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_2, 0)) AS pm_actuals_month_2,
        SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_3, 0)) AS pm_actuals_month_3, SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_4, 0)) AS pm_actuals_month_4,
        SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_5, 0)) AS pm_actuals_month_5, SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_6, 0)) AS pm_actuals_month_6,
        SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_7, 0)) AS pm_actuals_month_7, SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_8, 0)) AS pm_actuals_month_8,
        SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_9, 0)) AS pm_actuals_month_9, SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_10, 0)) AS pm_actuals_month_10,
        SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_11, 0)) AS pm_actuals_month_11, SUM(IF(o.type = ‘PM Actuals (Invoiced) Outlay $:’, month_12, 0)) AS pm_actuals_month_12,(g.obligation_month_1 * budget_centers.total_budget) / 100 AS obligation_goal_month_1,
        (g.obligation_month_2 * budget_centers.total_budget) / 100 AS obligation_goal_month_2,
        (g.obligation_month_3 * budget_centers.total_budget) / 100 AS obligation_goal_month_3,
        (g.obligation_month_4 * budget_centers.total_budget) / 100 AS obligation_goal_month_4,
        (g.obligation_month_5 * budget_centers.total_budget) / 100 AS obligation_goal_month_5,
        (g.obligation_month_6 * budget_centers.total_budget) / 100 AS obligation_goal_month_6,
        (g.obligation_month_7 * budget_centers.total_budget) / 100 AS obligation_goal_month_7,
        (g.obligation_month_8 * budget_centers.total_budget) / 100 AS obligation_goal_month_8,
        (g.obligation_month_9 * budget_centers.total_budget) / 100 AS obligation_goal_month_9,
        (g.obligation_month_10 * budget_centers.total_budget) / 100 AS obligation_goal_month_10,
        (g.obligation_month_11 * budget_centers.total_budget) / 100 AS obligation_goal_month_11,
        (g.obligation_month_12 * budget_centers.total_budget) / 100 AS obligation_goal_month_12,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_1 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_1,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_2 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_2,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_3 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_3,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_4 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_4,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_5 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_5,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_6 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_6,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_7 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_7,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_8 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_8,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_9 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_9,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_10 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_10,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_11 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_11,
        SUM(IF(o.type = ‘Reported FACTS Obligated $:’, (month_12 / budget_centers.total_budget * 100), 0)) AS obligation_percent_month_12,

        (g.outlay_month_1 * budget_centers.total_budget) / 100 AS outlay_goal_month_1,
        (g.outlay_month_2 * budget_centers.total_budget) / 100 AS outlay_goal_month_2,
        (g.outlay_month_3 * budget_centers.total_budget) / 100 AS outlay_goal_month_3,
        (g.outlay_month_4 * budget_centers.total_budget) / 100 AS outlay_goal_month_4,
        (g.outlay_month_5 * budget_centers.total_budget) / 100 AS outlay_goal_month_5,
        (g.outlay_month_6 * budget_centers.total_budget) / 100 AS outlay_goal_month_6,
        (g.outlay_month_7 * budget_centers.total_budget) / 100 AS outlay_goal_month_7,
        (g.outlay_month_8 * budget_centers.total_budget) / 100 AS outlay_goal_month_8,
        (g.outlay_month_9 * budget_centers.total_budget) / 100 AS outlay_goal_month_9,
        (g.outlay_month_10 * budget_centers.total_budget) / 100 AS outlay_goal_month_10,
        (g.outlay_month_11 * budget_centers.total_budget) / 100 AS outlay_goal_month_11,
        (g.outlay_month_12 * budget_centers.total_budget) / 100 AS outlay_goal_month_12,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_1 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_1,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_2 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_2,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_3 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_3,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_4 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_4,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_5 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_5,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_6 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_6,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_7 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_7,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_8 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_8,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_9 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_9,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_10 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_10,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_11 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_11,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, (month_12 / budget_centers.total_budget * 100), 0)) AS outlay_percent_month_12

        FROM projects
        RIGHT JOIN budget_centers ON projects.uid = budget_centers.project_id
        RIGHT JOIN budget_center_contracts ON budget_center_contracts.budget_center_id = budget_centers.uid
        RIGHT JOIN obligations_outlays AS o ON o.project_id = projects.uid
        RIGHT JOIN obligations_outlays_goals AS g ON projects.appropriation = g.appropriation
        WHERE o.year = 2013 AND o.year_count = 1 AND (o.type = ‘Reported FACTS Obligated $:’ OR o.type = ‘Outlay $ (Reported in FACTS)’ OR o.type = ‘PM Actuals (Invoiced) Outlay $:’)
        AND g.year = 1
        GROUP BY funding_id