Dong Shin 08.26.2013

  • continue working on FA
    • working on Query Builder
    • working on queries for Chris
      • saving queries to QueryBuilderQueries.sql in resources/database/
    • adding Obligations/Outlays to the Query Builder
      • query to find obligaed, outlay, pm actuals
      • SELECT projects.*, bc.*, bcc.*,
        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 = ‘Outlay $ (Reported in FACTS)’, month_11, 0)) AS outlay_month_11,
        SUM(IF(o.type = ‘Outlay $ (Reported in FACTS)’, month_12, 0)) AS outlay_month_12,

        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

        FROM projects
        RIGHT JOIN budget_centers AS bc ON projects.uid = bc.project_id
        RIGHT JOIN budget_center_contracts AS bcc ON bcc.budget_center_id = bc.uid
        RIGHT JOIN obligations_outlays AS o ON o.project_id = projects.uid
        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 $:’)
        GROUP BY funding_id