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….

Phil 3.14.14

8:00 – 4:00 SR

and a happy Pi day to you!
Deployed new vis
Made charts
Made slide show
Made tables
Ran script to populate tables
Success!
JavaScript
- fix ATR link
- fix ipsum lorem
- Finally figured out the problem with scrollto. The time is in milliseconds. The examples are incorrect, but the documentation is right.
- Commenting
- start on webgl

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

Phil 3.13.14

8:00 – 5:00 SR

Backups
Charts! Made one, decided that it might be easier to update the Trend Widget to point at different data sources. Wound up allowing edits on the XML in the desktop “save as” option. Built a release and sent to Bill V.
Deploying new FA
JavaScript
- Making text unselectable: http://stackoverflow.com/questions/826782/css-rule-to-disable-text-selection-highlighting
- Based on some user testing, I may need to make it so that clicking toggles the selection of a node, rather than selection while mousedown.

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

Phil 3.12.14

8:00 – 4:00 SR

Backups
Deployed new FA, and a *huge* view
Lenny is having a problem where EAs that have been claimed are getting unclaimed.
Printed slideshow screenshots that we need to match
Had the evil “Main application must be in the list of application paths” message. This page had the answer. It looks like the mxml file had been dropped from the project file.
Found the code and the format for building reusable charts. Dong’s building the queries. We’ll try them out tomorrow.
JavaScript

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

Phil 8.11.14

8:00 – 5:30 SR

Backups
More Server Cert work
Discussions with Lenny
Need to add a search to the page that will find text in paragraphs and link back to the Physics Shape. All paragraphs that don’t match the text are hidden? Wonder how that will affect the scrollto. Also need an introduction paragraph and a title.
When a cognos data is manually mapped, obligations and outlays should be added to monthly financial data
Second year query needs to be fixed for month order
Need to add a pop-up warning about claiming a req where the subBC doesn’t match.
RQ notes
- Add a show/hide hidden fields. This does mean that when checked, an item disappears
- Add “strong hide” that keeps the line in the DB, but never shows the line
- Carry through ignore from previous steps. In other words, ignore “hidden” items from subsequent queries
JavaScript
- Figured out how to grab a redirect request and use it in the context of the operation. Also how to prevent default behavior.
- Added content. Discovered more about stylesheets.
- The descriptor text for Physics shapes was disappearing after a mousup event on the scrollable list. Added a refresh call to the ShapeManager.
Helped Dong a bit with data providers.

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 $:’)

Phil 3.10.14

8:00 – 4:00 SR

Backups
Started server certs renewal
There are still some problems with lost req’s. Got a screenshot that has all the pieces.
Javascript
- Find out while null objects are being returned in events. It turns out that the “id” tag cannot have any special characters in it, or Y.one()/Y.all() will fail. Added a “safeIdString” variable to the PhysicsShape class that runs a regex [^0-9-a-Z] on all names and copies the result to the variable. This still means that the id tag for paragraphs, etc needs to be safe, but it also allows the shapes to show the full name.
- Discovered that my Chrome browser on my dev machine is broken. Tooltip formatting is wrong and hover does not work at all. Need to reinstall. And I.E. is still slooooooow with all this

Dong Shin 03.07.2014

deployed SQL’s update the obligations/outlays to 100% – works!
found that some of the COGNOS contracts data have budget_center_id set to 0, it should be null or some budget_center_id. This caused the data not to show up in claimed/unclaimed contracts in Req Info panel
- cleaned up 0’s and some of the data that were deleted
- need a SQL to clean up before mapping?
looked at Carla’s project saving error, caused by long budget center name from budget_center_names table. cleaned up the table.

Phil 3.7.14

8:00 – xxx SR

Backups
Deployed sql fix for update obligations_outlays to 100%
budget_center_id is being set to zero????
Cleaned up budget center names

Dong Shin 03.06.2014

trying to figure out if months can be updated in a single query……. instead of separate queries for each month!
- UPDATE obligations_outlays oo
  SET month_12 = CASE
  WHEN (month_11 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_11
  WHEN (month_10 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_10
  WHEN (month_9 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_9
  WHEN (month_8 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_8
  WHEN (month_7 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_7
  WHEN (month_6 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_6
  WHEN (month_5 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_5
  WHEN (month_4 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_4
  WHEN (month_3 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_3
  WHEN (month_2 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_2
  WHEN (month_1 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_1
  ELSE month_12 END,
  month_11 = CASE
  …………………….
  month_2 = CASE
  WHEN (month_1 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_1
  ELSE month_2 END
  WHERE oo.year_count = 1
  AND (oo.type = ‘Outlay $ (Reported in FACTS)’ OR oo.type = ‘Reported FACTS Obligated $:’)
finally got the query working! – that update first month second year when last month first year is at 100%
- UPDATE obligations_outlays oo1
  LEFT JOIN obligations_outlays oo2
  ON (oo1.project_id = oo2.project_id
  AND oo1.funding_id = oo2.funding_id
  AND oo1.type = oo2.type
  AND oo1.uid <> oo2.uid)
  LEFT JOIN budget_centers bc
  ON( oo1.funding_id = bc.uid)
  AND (oo1.type = ‘Outlay $ (Reported in FACTS)’ OR oo1.type = ‘Reported FACTS Obligated $:’)
  AND (oo2.month_12 >= bc.total_budget)
  SET oo1.month_1 = oo2.month_12
  WHERE oo1.year_count = 2 AND oo2.year_count = 1
  AND (oo1.type = ‘Outlay $ (Reported in FACTS)’ OR oo1.type = ‘Reported FACTS Obligated $:’)
added months updates queries tor year 2 in Post Processing
talked Kelley yesterday and passed along the contact info, she said she would email me the details, never did
- emailed Kelley this morning and got response, forward it to Chris

Phil 3.6.14

8:00 – 4:00 SR

Backups
Renewed the viztool urn and deleted visibility. Well, tried, anyway.
JavaScript
- Add scrollto text based on item clicked event – Done, but now the scrolling function seems kinda messed up. The scroller wants to bounce back to some point that seems to be related to long contact drag times. Short flicks work?
- See if scrolling can fire an event that can be used to get the current visible text (id? label?) that can be used to determine what to show in the network chart. Alternatively, listen to click events from the wrapping node or potentially the <p> items. Done!
- Break slideshow into sections that are relevant to the clicked items.

viztales

Dimension reduction, State, Orientation, and Speed

Dong Shin 03.17.2014

Dong Shin 03.14.2014

Phil 3.14.14

Dong Shin 03.13.2014

Phil 3.13.14

Dong Shin 03.12.2014

Phil 3.12.14

Dong Shin 03.11.2014

Phil 8.11.14

Dong Shin 03.10.2014

Phil 3.10.14

Dong Shin 03.07.2014

Phil 3.7.14

Dong Shin 03.06.2014

Phil 3.6.14