Author Archives: gvr4wd

Unknown's avatar

About gvr4wd

...hmmm

Dong Shin 03.28.2014

  • reworking Req Data Analysis using the new view – __view_project_detailed_data
    • done… lot faster than before!
    • committed amounts not cumulative, working on it. fixed! using functions….
  • created few support functions in mysql
    • get_cumulative_committed_amount(budget_center_id, year_count, month_number)
    • get_fiscal_month (Date)
    • get_fiscal_year (Date)
  • using MySQL Workbench instead of phpMyAdmin – more robust and convenient!

Dong Shin 03.27.2014

  • working on generating big data table for FA. __view_project_detailed_data
    • created sub views to work-around nested SELECT limitation in view
      • __view_yearly_totals_by_project, __view_financial_analysts_by_project, __view_portfolio_mgrs_by_project, __view_total_committed_amount_by_budget_center, __view_service_pocs_by_budget_center
    • remember not t use nested SELECTs in views!!!!
    • adding monthly committed amounts take too long to process….. 3 sec compared to 1 sec…
    • *THIS* is the join that produces a year data per row that has all obligations, outlays, and goals!
      • SELECT SUM(IF(oo.type LIKE ‘%Planned%Oblig%’,1,0)) AS planned_obligated_count,
        SUM(IF(oo.type LIKE ‘%Reported%Oblig%’,1,0)) AS reported_outlay_count,
        SUM(IF(oo.type LIKE ‘%Planned%Outlay%’,1,0)) AS planned_outlay_count,
        SUM(IF(oo.type LIKE ‘%Reported%Outlay%’,1,0)) AS reported_outlay_count,
        SUM(IF(oo.type LIKE ‘%PM%Actuals%’,1,0)) AS pm_actuals_count,
        SUM(IF(oo.type LIKE ‘%Outlay%FACTS%’,1,0)) AS outlay_facts_count,
        p.*, bc.*
        FROM projects p
        LEFT JOIN budget_centers bc ON bc.project_id = p.uid
        LEFT JOIN obligations_outlays oo ON oo.funding_id = bc.uid
        LEFT JOIN obligations_outlays_goals oog ON oog.appropriation = p.appropriation AND oog.year = oo.year_count
        LEFT JOIN committed_amounts ca ON ca.budget_center_id = bc.uid AND (bc.fiscal_year – YEAR(ca.committed_date) + 1) = oo.year_count
        LEFT JOIN __view_yearly_totals_by_project AS yt ON yt.project_id = p.uid
        LEFT JOIN __view_financial_analysts_by_project AS fa ON fa.project_id = p.uid
        LEFT JOIN __view_portfolio_mgrs_by_project pm ON pm.project_id = p.uid
        LEFT JOIN __view_service_pocs_by_budget_center AS spbc ON spbc.budget_center_id = bc.uid
        LEFT JOIN __view_total_committed_amount_by_budget_center ca_totals ON ca_totals.budget_center_id = bc.uid
        GROUP BY oo.funding_id, oo.year_count

Dong Shin 03.26.2014

  • deployed new views and presentation looks better!
  • few bugs
    • fix __view_financial_data – add sum and group by for year 2
    • Planned Obligations/Outlay Update SQL errors when it reaches 100% – uid, project_id, year_count are null
    • Recalculate Obligations/Outlays when Claimed/Unclaimed Contracts are updated
    • rework Query Builder!
  • bc.total_budget for Req Allocation and vct.committed_amount for Req Funded
  • downloaded and played with JSPMyAdmin – JSP based MySQL Admin tool that can run on Tomcat…. a bit slow and looks like missing lots of features compared to phpMyAdmin, not sure if I want to use it.
  • working on getting entire data for big table
    • this may be good enough?
      • SELECT *
        FROM projects p
        LEFT JOIN budget_centers bc ON bc.project_id = p.uid
        LEFT JOIN obligations_outlays oo ON oo.funding_id = bc.uid
        LEFT JOIN (SELECT project_id, GROUP_CONCAT(fiscal_year) AS fiscal_years,
        SUM(budget_amount) AS budget_amount FROM yearly_totals GROUP BY project_id) AS yt ON yt.project_id = p.uid
        LEFT JOIN (SELECT project_id, GROUP_CONCAT(login) AS financial_analysts FROM _projects_portfolio_mgrs GROUP BY project_id) AS fa ON fa.project_id = p.uid
        LEFT JOIN (SELECT project_id, GROUP_CONCAT(login) AS portfolio_managers FROM _projects_portfolio_admins GROUP BY project_id) AS pm ON pm.project_id = p.uid
        LEFT JOIN (SELECT budget_center_id, SUM(committed_amount) AS total_committed_amount, GROUP_CONCAT(committed_date) AS committed_dates
        FROM committed_amounts GROUP BY budget_center_id) AS ca ON ca.budget_center_id = bc.uid

Dong Shin 03.25.2014

  • VizTool review
    • All fields with $ should accept negative values
    • get a list of budget values for FY13 and FY14 charts
    • create a query for Planned Obligated vs. FACTS
    • Outlay $ Reported row should not be editable
    • invoice dialog box should not show if the line item is not a MIPR or EAO
    • total budget in slides should be project total, not requistiion total
    • Req Data Analysis should show Req Allocated, Req Funded (Committed)
  • reworked __view_monthly_committed and __view_financial_data
    • returns view in projects instead of requisitions….

Dong Shin 03.24.2014

  • reworked all stored queries for briefing data to have 5 months, fixed month_2 typos – user_queries.sql
  • removed year 3 and 4 to speed up __view_financial_data view – DBUpdateEnhancements.032414.sql
  • created queries for Lenny – summary of FY13 and FY14 by appropriation and capability
    • FY13 Summary by Appropriation and Capability, FY14 Summary by Appropriation and Capability
    • select capability, appropriation,
      SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, bc.total_budget, 0)) AS total_budget,
      SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, bc.funded_budget, 0)) AS funded_budget,
      SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, oo.month_5, 0)) AS ‘Obligated Feb 2013’,

      SUM(IF(oo.type = ‘Outlay $ (Reported in FACTS)’, oo.month_5, 0)) AS ‘Outlay Feb 2013’,

      SUM(IF(oo.type = ‘PM Actuals (Invoiced) Outlay $:’, oo.month_5, 0)) AS ‘PM Actuals Feb 2013’,

      SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, committed_amount,0 )) AS total_committed
      from projects p
      LEFT JOIN budget_centers bc ON p.uid = bc.project_id
      LEFT JOIN obligations_outlays oo ON p.uid = oo.project_id AND bc.uid = oo.funding_id
      LEFT JOIN __view_committed_totals vct ON bc.uid = vct.budget_center_id
      where fiscal_year = 2013 AND oo.year_count = 2 AND
      (oo.type = ‘Outlay $ (Reported in FACTS)’ OR
      oo.type = ‘Reported FACTS Obligated $:’ OR
      oo.type = ‘PM Actuals (Invoiced) Outlay $:’)
      GROUP BY capability, appropriation

Dong Shin 03.21.2014

  • need to remove current month from the queries for the reports/presencation
  • tried to create view, __view_financial_data, only to find out that variables are *NOT* allowed! Ugh, need to find a workaround.
    • created a trigger –  update_tmp_view_financial_data  to update __tmp_view_financial_data on obligations/outlays update. this deletes all entries and regenerates financial data whenever obligations/outlays are updated….
      • CREATE DEFINER=`root`@`localhost` TRIGGER `update_tmp_view_financial_data` AFTER UPDATE ON `obligations_outlays` FOR EACH ROW begin delete from __tmp_view_financial_data; call generate_financial_data(); end
    • created stored procedure – generate_financial_data()
    • created a table – __tmp_view_financial_data from the query in DBUpdateEnhancements.032114.sql
  • working on refining queries for financial data
    • currently takes ~8 seconds for the query
    • optimized to 0.5 using user defined variable for year_total
      • select ‘Committed’ AS oo_type, project_number, title, begin_year, end_year, center_name, center_number,
        appropriation, type, capability, expenditure_center, investment_portfolio, program_element,
        facts_pe, sub_center_name, sub_center_number, req_id, req_type, total_budget, funded_budget,
        fiscal_year, year_count,
        @year_total := IFNULL((SELECT SUM(year_total) FROM __tmp_monthly_committed vmc2 WHERE vmc1.req_id = vmc2.req_id
        AND year_count < 4 GROUP BY vmc2.req_id),0) as year_total ,
        @year_total + month_1 AS month_1,
        @year_total + month_1+month_2 AS month_2,
        @year_total + month_1+month_2+month_3 AS month_3,
        @year_total + month_1+month_2+month_3+month_4 AS month_4,
        @year_total + month_1+month_2+month_3+month_4+month_5 AS month_5,
        @year_total + month_1+month_2+month_3+month_4+month_5+month_6 AS month_6,
        @year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7 AS month_7,
        @year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7+month_8 AS month_8,
        @year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7+month_8+month_9 AS month_9,
        @year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7+month_8+month_9+month_10 AS month_10,
        @year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7+month_8+month_9+month_10+month_11 AS month_11,
        @year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7+month_8+month_9+month_10+month_11+month_12 AS month_12
        FROM __tmp_monthly_committed vmc1
        WHERE year_count = 4

Dong Shin 03.20.2014

  • deployed new view – __view_monthly_committed and FA
  • spent most of the day fixing SQL’s and scripts for the charts……
    • need to make sure to use Outlay $ (Reported in FACTS) instead of Reported Outlay $:
    • both Obligations and Outlays need second year updates
    • fixed stored queries (2013 and 2014) that uses month_2!!!
  • performance of __view_financial_data using __view_monthly_committed is very bad… may have to remove the year 3 and 4 for better performance as without it cuts down half time….

Dong Shin 03.19.2014

  • deployed new FA/queries
    • found that committed amounts are not generated when there is no data for the year – fixed
    • fixed various errors from Lenny
  • ReqDataAnalysis now has combination of bar and area just like the presentation…
  • new query slows down the __view_financial_data…
  • new __view_monthly_committed view
    • CREATE ALGORITHM = UNDEFINED VIEW `__view_monthly_committed` AS
      select ‘Committed Monthly’ AS `oo_type`,`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,`p`.`begin_year` AS `begin_year`,
      `p`.`end_year` AS `end_year`,`p`.`center_name` AS `center_name`,`p`.`center_number` AS `center_number`,`p`.`appropriation` AS `appropriation`,
      `p`.`type` AS `type`,`p`.`capability` AS `capability`,`p`.`expenditure_center` AS `expenditure_center`,
      `p`.`investment_portfolio` AS `investment_portfolio`,`p`.`program_element` AS `program_element`,`p`.`facts_pe` AS `facts_pe`,
      `p`.`sub_center_name` AS `sub_center_name`,`p`.`sub_center_number` AS `sub_center_number`,`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
      `bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
      o.year_count AS `year_count`,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0), 0)) as month_1,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0), 0)) as month_2,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0), 0)) as month_3,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0), 0)) as month_4,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0), 0)) as month_5,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0), 0)) as month_6,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0), 0)) as month_7,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0), 0)) as month_8,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0), 0)) as month_9,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0), 0)) as month_10,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0), 0)) as month_11,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0), 0)) as month_12,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), `ca`.`committed_amount`, 0)) as year_total
      from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
      left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
      left join obligations_outlays o on p.uid = o.project_id AND o.funding_id = bc.uid AND o.type = ‘Reported Outlay $:’
      group by `ca`.`budget_center_id`,o.year_count;

Dong Shin 03.18.2014

  • found that Committed Amounts not rolling over from previous year…
    • created a view to store monthly committed amounts in a year – __view_monthly_committed
    • reworked __view_financial_data view for the __view_monthly_committed

Dong Shin 03.17.2014

  • working on queries for reports
  • required Appropriation/Capabilities combo for 2013
    • O&M – ACC, GCC, TRCO
    • RDT&E – ACC, CCRSE, GCC, MCC, TRCO
    • PROC – GCC, TRCO
  • created python script to generate 2013 data – generate_report_data_2013.py
  • reworked _view_financial_data to include cumulative committed amounts – DBUpdateEnhancements.031714.sql
    • CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `__view_financial_data` AS 

      select ‘Obligation Goal’ AS `oo_type`,`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,`p`.`begin_year` AS `begin_year`,
      `p`.`end_year` AS `end_year`,`p`.`center_name` AS `center_name`,`p`.`center_number` AS `center_number`,`p`.`appropriation` AS `appropriation`,
      `p`.`type` AS `type`,`p`.`capability` AS `capability`,`p`.`expenditure_center` AS `expenditure_center`,
      `p`.`investment_portfolio` AS `investment_portfolio`,`p`.`program_element` AS `program_element`,
      `p`.`facts_pe` AS `facts_pe`,`p`.`sub_center_name` AS `sub_center_name`,`p`.`sub_center_number` AS `sub_center_number`,
      `bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,
      `bc`.`fiscal_year` AS `fiscal_year`,`oo`.`year` AS `year_count`,
      ((`bc`.`funded_budget` * `oo`.`obligation_month_1`) / 100) AS `month_1`,
      ((`bc`.`funded_budget` * `oo`.`obligation_month_2`) / 100) AS `month_2`,
      ((`bc`.`funded_budget` * `oo`.`obligation_month_3`) / 100) AS `month_3`,
      ((`bc`.`funded_budget` * `oo`.`obligation_month_4`) / 100) AS `month_4`,
      ((`bc`.`funded_budget` * `oo`.`obligation_month_5`) / 100) AS `month_5`,
      ((`bc`.`funded_budget` * `oo`.`obligation_month_6`) / 100) AS `month_6`,
      ((`bc`.`funded_budget` * `oo`.`obligation_month_7`) / 100) AS `month_7`,
      ((`bc`.`funded_budget` * `oo`.`obligation_month_8`) / 100) AS `month_8`,
      ((`bc`.`funded_budget` * `oo`.`obligation_month_9`) / 100) AS `month_9`,
      ((`bc`.`funded_budget` * `oo`.`obligation_month_10`) / 100) AS `month_10`,
      ((`bc`.`funded_budget` * `oo`.`obligation_month_11`) / 100) AS `month_11`,
      ((`bc`.`funded_budget` * `oo`.`obligation_month_12`) / 100) AS `month_12`
      from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
      left join `obligations_outlays_goals` `oo` on((`p`.`appropriation` = `oo`.`appropriation`)))
      union
      select ‘Outlay Goal’ AS `oo_type`,`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,`p`.`begin_year` AS `begin_year`,
      `p`.`end_year` AS `end_year`,`p`.`center_name` AS `center_name`,`p`.`center_number` AS `center_number`,`p`.`appropriation` AS `appropriation`,
      `p`.`type` AS `type`,`p`.`capability` AS `capability`,`p`.`expenditure_center` AS `expenditure_center`,
      `p`.`investment_portfolio` AS `investment_portfolio`,`p`.`program_element` AS `program_element`,`p`.`facts_pe` AS `facts_pe`,
      `p`.`sub_center_name` AS `sub_center_name`,`p`.`sub_center_number` AS `sub_center_number`,`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
      `bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,`oo`.`year` AS `year_count`,
      ((`bc`.`funded_budget` * `oo`.`outlay_month_1`) / 100) AS `month_1`,
      ((`bc`.`funded_budget` * `oo`.`outlay_month_2`) / 100) AS `month_2`,
      ((`bc`.`funded_budget` * `oo`.`outlay_month_3`) / 100) AS `month_3`,
      ((`bc`.`funded_budget` * `oo`.`outlay_month_4`) / 100) AS `month_4`,
      ((`bc`.`funded_budget` * `oo`.`outlay_month_5`) / 100) AS `month_5`,
      ((`bc`.`funded_budget` * `oo`.`outlay_month_6`) / 100) AS `month_6`,
      ((`bc`.`funded_budget` * `oo`.`outlay_month_7`) / 100) AS `month_7`,
      ((`bc`.`funded_budget` * `oo`.`outlay_month_8`) / 100) AS `month_8`,
      ((`bc`.`funded_budget` * `oo`.`outlay_month_9`) / 100) AS `month_9`,
      ((`bc`.`funded_budget` * `oo`.`outlay_month_10`) / 100) AS `month_10`,
      ((`bc`.`funded_budget` * `oo`.`outlay_month_11`) / 100) AS `month_11`,
      ((`bc`.`funded_budget` * `oo`.`outlay_month_12`) / 100) AS `month_12`
      from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
      left join `obligations_outlays_goals` `oo` on((`p`.`appropriation` = `oo`.`appropriation`)))
      union
      select ‘Committed Monthly’ AS `oo_type`,`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,`p`.`begin_year` AS `begin_year`,
      `p`.`end_year` AS `end_year`,`p`.`center_name` AS `center_name`,`p`.`center_number` AS `center_number`,`p`.`appropriation` AS `appropriation`,
      `p`.`type` AS `type`,`p`.`capability` AS `capability`,`p`.`expenditure_center` AS `expenditure_center`,
      `p`.`investment_portfolio` AS `investment_portfolio`,`p`.`program_element` AS `program_element`,`p`.`facts_pe` AS `facts_pe`,
      `p`.`sub_center_name` AS `sub_center_name`,`p`.`sub_center_number` AS `sub_center_number`,`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
      `bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
      ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`) + 1) AS `year_count`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) AS `month_1`,
      sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) AS `month_2`,
      sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) AS `month_3`,
      sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) AS `month_4`,
      sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) AS `month_5`,
      sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) AS `month_6`,
      sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) AS `month_7`,
      sum(if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0)) AS `month_8`,
      sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) AS `month_9`,
      sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) AS `month_10`,
      sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) AS `month_11`,
      sum(if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0)) AS `month_12`
      from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
      left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
      group by `ca`.`budget_center_id`,if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`))
      union
      select ‘Committed’ AS `oo_type`,
      `p`.`project_number` AS `project_number`,`p`.`title` AS `title`,
      `p`.`begin_year` AS `begin_year`,
      `p`.`end_year` AS `end_year`,
      `p`.`center_name` AS `center_name`,
      `p`.`center_number` AS `center_number`,
      `p`.`appropriation` AS `appropriation`,
      `p`.`type` AS `type`,
      `p`.`capability` AS `capability`,
      `p`.`expenditure_center` AS `expenditure_center`,
      `p`.`investment_portfolio` AS `investment_portfolio`,
      `p`.`program_element` AS `program_element`,
      `p`.`facts_pe` AS `facts_pe`,
      `p`.`sub_center_name` AS `sub_center_name`,
      `p`.`sub_center_number` AS `sub_center_number`,
      `bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
      `bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
      ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`) + 1) AS `year_count`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) AS `month_1`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) AS `month_2`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) AS `month_3`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) AS `month_4`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) AS `month_5`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) AS `month_6`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) AS `month_7`,
      sum(if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0)) AS `month_8`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) AS `month_9`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) AS `month_10`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) AS `month_11`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0)) AS `month_12`
      from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
      left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
      WHERE ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`)) = 3
      group by `ca`.`budget_center_id`,if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`))
      UNION
      select ‘Committed’ AS `oo_type`,
      `p`.`project_number` AS `project_number`,`p`.`title` AS `title`,
      `p`.`begin_year` AS `begin_year`,
      `p`.`end_year` AS `end_year`,
      `p`.`center_name` AS `center_name`,
      `p`.`center_number` AS `center_number`,
      `p`.`appropriation` AS `appropriation`,
      `p`.`type` AS `type`,
      `p`.`capability` AS `capability`,
      `p`.`expenditure_center` AS `expenditure_center`,
      `p`.`investment_portfolio` AS `investment_portfolio`,
      `p`.`program_element` AS `program_element`,
      `p`.`facts_pe` AS `facts_pe`,
      `p`.`sub_center_name` AS `sub_center_name`,
      `p`.`sub_center_number` AS `sub_center_number`,
      `bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
      `bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
      ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`) + 1) AS `year_count`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) AS `month_1`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) AS `month_2`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) AS `month_3`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) AS `month_4`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) AS `month_5`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) AS `month_6`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) AS `month_7`,
      sum(if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0)) AS `month_8`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) AS `month_9`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) AS `month_10`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) AS `month_11`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0)) AS `month_12`
      from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
      left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
      WHERE ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`)) = 2
      group by `ca`.`budget_center_id`,if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`))
      UNION
      select ‘Committed’ AS `oo_type`,
      `p`.`project_number` AS `project_number`,`p`.`title` AS `title`,
      `p`.`begin_year` AS `begin_year`,
      `p`.`end_year` AS `end_year`,
      `p`.`center_name` AS `center_name`,
      `p`.`center_number` AS `center_number`,
      `p`.`appropriation` AS `appropriation`,
      `p`.`type` AS `type`,
      `p`.`capability` AS `capability`,
      `p`.`expenditure_center` AS `expenditure_center`,
      `p`.`investment_portfolio` AS `investment_portfolio`,
      `p`.`program_element` AS `program_element`,
      `p`.`facts_pe` AS `facts_pe`,
      `p`.`sub_center_name` AS `sub_center_name`,
      `p`.`sub_center_number` AS `sub_center_number`,
      `bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
      `bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
      ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`) + 1) AS `year_count`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) AS `month_1`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) AS `month_2`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) AS `month_3`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) AS `month_4`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) AS `month_5`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) AS `month_6`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) AS `month_7`,
      sum(if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0)) AS `month_8`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) AS `month_9`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) AS `month_10`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) AS `month_11`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0)) AS `month_12`
      from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
      left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
      WHERE ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`)) = 1
      group by `ca`.`budget_center_id`,if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`))
      UNION
      select ‘Committed’ AS `oo_type`,
      `p`.`project_number` AS `project_number`,`p`.`title` AS `title`,
      `p`.`begin_year` AS `begin_year`,
      `p`.`end_year` AS `end_year`,
      `p`.`center_name` AS `center_name`,
      `p`.`center_number` AS `center_number`,
      `p`.`appropriation` AS `appropriation`,
      `p`.`type` AS `type`,
      `p`.`capability` AS `capability`,
      `p`.`expenditure_center` AS `expenditure_center`,
      `p`.`investment_portfolio` AS `investment_portfolio`,
      `p`.`program_element` AS `program_element`,
      `p`.`facts_pe` AS `facts_pe`,
      `p`.`sub_center_name` AS `sub_center_name`,
      `p`.`sub_center_number` AS `sub_center_number`,
      `bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
      `bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
      ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`) + 1) AS `year_count`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) AS `month_1`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) AS `month_2`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) AS `month_3`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) AS `month_4`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) AS `month_5`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) AS `month_6`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) AS `month_7`,
      sum(if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0)) AS `month_8`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) AS `month_9`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) AS `month_10`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) AS `month_11`,
      sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0)) AS `month_12`
      from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
      left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
      WHERE ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`)) = 0
      group by `ca`.`budget_center_id`,if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`))
      union
      select if((`oo`.`type` = ‘Reported Outlay $:’),’Outlays’,if((`oo`.`type` = ‘Reported FACTS Obligated $:’),’Obligated’,’PM Actuals’)) AS `oo_type`,
      `p`.`project_number` AS `project_number`,`p`.`title` AS `title`,`p`.`begin_year` AS `begin_year`,`p`.`end_year` AS `end_year`,
      `p`.`center_name` AS `center_name`,`p`.`center_number` AS `center_number`,`p`.`appropriation` AS `appropriation`,`p`.`type` AS `type`,
      `p`.`capability` AS `capability`,`p`.`expenditure_center` AS `expenditure_center`,`p`.`investment_portfolio` AS `investment_portfolio`,
      `p`.`program_element` AS `program_element`,`p`.`facts_pe` AS `facts_pe`,`p`.`sub_center_name` AS `sub_center_name`,
      `p`.`sub_center_number` AS `sub_center_number`,`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,`bc`.`total_budget` AS `total_budget`,
      `bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,`oo`.`year_count` AS `year_count`,
      `oo`.`month_1` AS `month_1`,`oo`.`month_2` AS `month_2`,`oo`.`month_3` AS `month_3`,`oo`.`month_4` AS `month_4`,`oo`.`month_5` AS `month_5`,
      `oo`.`month_6` AS `month_6`,`oo`.`month_7` AS `month_7`,`oo`.`month_8` AS `month_8`,`oo`.`month_9` AS `month_9`,`oo`.`month_10` AS `month_10`,
      `oo`.`month_11` AS `month_11`,`oo`.`month_12` AS `month_12`
      from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
      left join `obligations_outlays` `oo` on(((`p`.`uid` = `oo`.`project_id`) and (`bc`.`uid` = `oo`.`funding_id`))))
      where ((`oo`.`type` = ‘Reported Outlay $:’) or (`oo`.`type` = ‘Reported FACTS Obligated $:’) or (`oo`.`type` = ‘PM Actuals (Invoiced) Outlay $:’));

Dong Shin 03.14.2014

  • fixed few SQL errors on-site. Month 5 Committed not calculated correctly, used month_2 throughout in the sql
  • generated data for the chart and worked!
  • changed Requisition Data to view in Advanced DataGrid in ReqDataAnalysis
    • added Currency label function to dollar columns
    • set initial year to current fiscal year
  • working on query to set committed amounts from previous year to next year….

Dong Shin 03.13.2014

  • deployed new FA, queries
  • updated committed_amounts to be cumulative in __view_financial_data
  • query to update chart data!
    • Insert new row with string replace…
      INSERT INTO table_dashboards (id, name, owner, xml_layout, published)
      SELECT null, name, owner, REPLACE(CONVERT(xml_layout USING utf8),’2013_RDTE_GCC’,’2014_RDTE_GCC’), published FROM table_dashboards
      WHERE id = 1
    • update a row using string replace…..
      UPDATE table_dashboards SET xml_layout=BINARY(REPLACE(CONVERT(xml_layout USING utf8),’2014_RDTE_GCC’,’2015_RDTE_GCC’))
      WHERE id = 2
  • query for committed amounts
    • SELECT bc.uid,bc.fiscal_year,
      YEAR(ca.committed_date)-bc.fiscal_year+1 AS year_count,
      SUM(IF(MONTH(committed_date)=1,committed_amount,0)) AS month_1,
      SUM(IF(MONTH(committed_date)<=2, committed_amount,0)) AS month_2,
      SUM(IF(MONTH(committed_date)<=3, committed_amount,0)) AS month_3,
      SUM(IF(MONTH(committed_date)<=4, committed_amount,0)) AS month_4,
      SUM(IF(MONTH(committed_date)<=5, committed_amount,0)) AS month_5,
      SUM(IF(MONTH(committed_date)<=6, committed_amount,0)) AS month_6,
      SUM(IF(MONTH(committed_date)<=7, committed_amount,0)) AS month_7,
      SUM(IF(MONTH(committed_date)<=8, committed_amount,0)) AS month_8,
      SUM(IF(MONTH(committed_date)<=9, committed_amount,0)) AS month_9,
      SUM(IF(MONTH(committed_date)<=10, committed_amount,0)) AS month_10,
      SUM(IF(MONTH(committed_date)<=11, committed_amount,0)) AS month_11,
      SUM(IF(MONTH(committed_date)<=12, committed_amount,0)) AS month_12,
      SUM(committed_amount),
      YEAR(ca.committed_date),
      MONTH(ca.committed_date)
      FROM budget_centers bc
      LEFT JOIN committed_amounts ca
      ON bc.uid = ca.budget_center_id

Dong Shin 03.12.2014

  • deployed FA and the new view __view_financial_data
    • committed need to rollover to next month
  • working on queries/scripts for reports
    • create queries for 2013, 2014 , each appropriation, and each capability – 30 total
    • created script to run the queries and save to Visibility database

Dong Shin 03.11.2014

  • trouble-shoot Req mapping problem, various of mistakes… noted on Phil’s blog
  • working on queries for reports
  • created Req Data Analysis panel and added to FA
  • __view_financial_data will be added to site

Dong Shin 03.10.2014

  • Lenny reported that Req 173756 doesn’t show Obligation Amount / Date in Claimed section… will take a look tomorrow
  • working on little Flex App to verify the Financial Data
  • reworked Financial Data query for report to generate all the data for all years
  • __view_financial_data contains….
    • SELECT ‘Obligation Goal’ AS oo_type,
      p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
      p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
      bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year, oo.year as year_count,
      (bc.funded_budget * obligation_month_1 / 100) as month_1,
      (bc.funded_budget * obligation_month_2 / 100) as month_2,
      (bc.funded_budget * obligation_month_3 / 100) as month_3,
      (bc.funded_budget * obligation_month_4 / 100) as month_4,
      (bc.funded_budget * obligation_month_5 / 100) as month_5,
      (bc.funded_budget * obligation_month_6 / 100) as month_6,
      (bc.funded_budget * obligation_month_7 / 100) as month_7,
      (bc.funded_budget * obligation_month_8 / 100) as month_8,
      (bc.funded_budget * obligation_month_9 / 100) as month_9,
      (bc.funded_budget * obligation_month_10 / 100) as month_10,
      (bc.funded_budget * obligation_month_11 / 100) as month_11,
      (bc.funded_budget * obligation_month_12 / 100) as month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN obligations_outlays_goals AS oo ON p.appropriation = oo.appropriation
      UNION
      SELECT ‘Outlay Goal’ AS oo_type,
      p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
      p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
      bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year, oo.year as year_count,
      (bc.funded_budget * outlay_month_1 / 100) as month_1,
      (bc.funded_budget * outlay_month_2 / 100) as month_2,
      (bc.funded_budget * outlay_month_3 / 100) as month_3,
      (bc.funded_budget * outlay_month_4 / 100) as month_4,
      (bc.funded_budget * outlay_month_5 / 100) as month_5,
      (bc.funded_budget * outlay_month_6 / 100) as month_6,
      (bc.funded_budget * outlay_month_7 / 100) as month_7,
      (bc.funded_budget * outlay_month_8 / 100) as month_8,
      (bc.funded_budget * outlay_month_9 / 100) as month_9,
      (bc.funded_budget * outlay_month_10 / 100) as month_10,
      (bc.funded_budget * outlay_month_11 / 100) as month_11,
      (bc.funded_budget * outlay_month_12 / 100) as month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN obligations_outlays_goals AS oo ON p.appropriation = oo.appropriation
      UNION
      SELECT ‘Committed’ AS oo_type,
      p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
      p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
      bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year,
      (IF(MONTH(committed_date)>=10, YEAR(committed_date)+1, YEAR(committed_date)) – bc.fiscal_year + 1) AS year_count,
      SUM(IF(month(committed_date)=10, committed_amount, 0)) as month_1,
      SUM(IF(month(committed_date)=11, committed_amount, 0)) as month_2,
      SUM(IF(month(committed_date)=12, committed_amount, 0)) as month_3,
      SUM(IF(month(committed_date)=1, committed_amount, 0)) as month_4,
      SUM(IF(month(committed_date)=2, committed_amount, 0)) as month_5,
      SUM(IF(month(committed_date)=3, committed_amount, 0)) as month_6,
      SUM(IF(month(committed_date)=4, committed_amount, 0)) as month_7,
      SUM(IF(month(committed_date)=5, committed_amount, 0)) as month_8,
      SUM(IF(month(committed_date)=6, committed_amount, 0)) as month_9,
      SUM(IF(month(committed_date)=7, committed_amount, 0)) as month_10,
      SUM(IF(month(committed_date)=8, committed_amount, 0)) as month_11,
      SUM(IF(month(committed_date)=9, committed_amount, 0)) as month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN committed_amounts AS ca ON ca.budget_center_id = bc.uid
      GROUP BY budget_center_id, IF(MONTH(committed_date)>=10, YEAR(committed_date)+1, YEAR(committed_date))
      UNION
      SELECT
      IF (oo.type = ‘Reported Outlay $:’, ‘Outlays’, IF(oo.type = ‘Reported FACTS Obligated $:’, ‘Obligated’, ‘PM Actuals’)) AS oo_type,
      p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
      p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
      bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year, oo.year_count,
      month_1, month_2, month_3, month_4, month_5, month_6, month_7, month_8, month_9, month_10, month_11, month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN obligations_outlays AS oo ON p.uid = oo.project_id AND bc.uid = oo.funding_id
      WHERE (oo.type = ‘Reported Outlay $:’ OR oo.type = ‘Reported FACTS Obligated $:’ OR oo.type = ‘PM Actuals (Invoiced) Outlay $:’)